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: 246960 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PSB SSChampion Points: 13195 More actions · Answer 5

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

Phil Parkin SSC Guru Points: 246960 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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: 246960 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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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!