Exclude square bracket and text from start or end of string

Question

Post reply

Exclude square bracket and text from start or end of string

PSB

SSChampion

Points: 13195

August 10, 2020 at 1:07 pm

#3778616

Hi,

I want to remove square brackets and text from either start of string or end of string . Requirements:

If the square bracket is found at the beginning of string and is followed by '-' then I want entire text after the '-' character and exclude everything from [] to '-'

If square bracket is found towards the end of string then remove square bracket and entire text with bracket and get all text before that. Thanks

CREATE TABLE #RemoveText ( ID INT,TextName NVARCHAR(100))
INSERT INTO  #RemoveText (ID,TextName)
SELECT '1','[Accounts Name] -  Transactional Process sessions' UNION 
        SELECT '2', 'SR1a - Agree on all scope changes [AST]' UNION
SELECT '3','No Change needed with parenthesis (target 98%)' UNION
                SELECT '4','ABCD text'


--Desired TextName
SELECT '1','Transactional Process sessions' UNION 
        SELECT '2', 'SR1a - Agree on all scope changes' UNION
SELECT '3','No Change needed with parenthesis (target 98%)' UNION
                SELECT '4','ABCD text'

DROP TABLE #RemoveText  ?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Here's one way.

SELECT CalcTextName = CASE
                          WHEN calcs2.Pos3 > 0 THEN
                              TRIM(RIGHT(rt.TextName, LEN(rt.TextName) - calcs2.Pos3))
                          WHEN calcs1.Pos1 > 0
                               AND calcs1.Pos2 > 0 THEN
                              TRIM(LEFT(rt.TextName, calcs1.Pos1 - 1))
                          ELSE
                              rt.TextName
                      END
FROM #RemoveText rt
    CROSS APPLY
(
    SELECT Pos1 = CHARINDEX('[', rt.TextName)
          ,Pos2 = CHARINDEX(']', rt.TextName)
) calcs1
    CROSS APPLY
(
    SELECT Pos3 = IIF(Pos2 > 0, CHARINDEX('-', rt.TextName, Pos2), 0)
) calcs2;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 2

Thanks for the solution. It works. How do I change this code for the hyphen part ?

The square bracket will not be followed by hyphen . It could have a space after ] or text could immediately start after ].

PSB SSChampion Points: 13195 More actions · Answer 3

Here is the code snippet

CREATE TABLE #RemoveText ( ID INT,TextName NVARCHAR(100))
INSERT INTO  #RemoveText (ID,TextName)
SELECT '1','[Accounts Name] Transactional Process sessions' UNION 
        SELECT '2', 'SR1a - Agree on all scope changes [AST]' UNION
SELECT '3','No Change needed with parenthesis (target 98%)' UNION
                SELECT '4','ABCD text' UNION 
                SELECT '5','[Accounts Name]New Transactional Process sessions'


--Desired TextName
SELECT '1','Transactional Process sessions' UNION 
        SELECT '2', 'SR1a - Agree on all scope changes' UNION
SELECT '3','No Change needed with parenthesis (target 98%)' UNION
                SELECT '4','ABCD text' Union
                SELECT '5','New Transactional Process sessions'

DROP TABLE #RemoveText  ?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

None of your revised test data matches this requirement:

If the square bracket is found at the beginning of string and is followed by '-' then I want entire text after the '-' character and exclude everything from [] to '-'

So it sounds like your requirement has changed. Or the data is duff. Please clarify.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 5

Yes, requirement has changed . There will be no more hyphen after [] .

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

OK, so the requirement now is "remove any square brackets and any text they may contain", is that correct?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 7

PSB

SSChampion

Points: 13195

August 26, 2020 at 5:43 pm

#3783854

Yes, that's correct . Thanks.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

Makes it easier. Try this.

SELECT CalcTextName = CASE
                          WHEN calcs1.Pos1 > 0
                               AND calcs1.Pos2 > calcs1.Pos1 THEN
                              TRIM(STUFF(rt.TextName, calcs1.Pos1, calcs1.Pos2 - calcs1.Pos1 + 1, ''))
                          ELSE
                              rt.TextName
                      END
FROM #RemoveText rt
    CROSS APPLY
(
    SELECT Pos1 = CHARINDEX('[', rt.TextName)
          ,Pos2 = CHARINDEX(']', rt.TextName)
) calcs1;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 9

PSB

SSChampion

Points: 13195

August 27, 2020 at 2:00 pm

#3784131

Works like a charm. Thank you!