CODEWITHSUNDEEP
sql-serverregext-sql
whytheq
whytheq

Reputation: 35557

Extracting a variable length string between braces

This is the basic structure of some strings in a column:

CREATE TABLE #M(ST VARCHAR(250));
INSERT INTO #M
values
    ('please help me world  (produced... but needs to go)'),
    ('please help me world Y (produced_this is extra extra extra long)'),
    ('please help me world Z (producedthis isshort)'),
    ('please help me world K (produced');

SELECT * 
FROM   #M;

What I need to do is extract this `(produced%)' whenever it is found.

I can find occurrences of rows with the offending snippet `(produced%)' easily enough viathis:

SELECT  * 
FROM    #M
WHERE   ST LIKE '%(PRODUCED%)%'

What I now need to do is delete the section from (produced up to the very next brace ) - as you can see the distance between these two phrases is variable.

After querying I'd hope to have the following results:

'please help me world X'
'please help me world Y'
'please help me world Z'
'please help me world K (produced'

Because there is no closing brace in the 4th statement it is left as is.

EDIT

There could be text to the right of the offending phrase - so a better example of some strings is the following:

CREATE TABLE #M(ST VARCHAR(250));
INSERT INTO #M
values
    ('please help me world  (produced... but needs to go) bb cc dd'),
    ('please help me world Y (produced_this is extra extra extra long)'),
    ('please help me world Z (producedthis isshort)'.xlsm),
    ('please help me world K (produced');

Upvotes: 0

Views: 94

Answers (1)

GarethD
GarethD

Reputation: 69759

You can identify where '(produced' appears in your string using CHARINDEX then just extract the characters to the left (using LEFT). Finally you would need this inside a case expression to only apply the logic where there is a closing parenthesis:

SELECT  NewST = CASE WHEN ST LIKE '%(PRODUCED%)%' 
                        THEN LEFT(ST, CHARINDEX('(PRODUCED', ST) - 1)
                    ELSE ST 
                END
FROM    #M;

This gives:

NewST
------------------------------
please help me world  
please help me world Y 
please help me world Z 
please help me world K (produced

Which appears to be the desired output.

EDIT

I guess ')' isn't always going to be the last part of the string, in which case you need to identify this too, by using the optional third argument of CHARINDEX which is the starting position. The basics are:

SELECT  *,
        CloseParenthesis = CHARINDEX(')', ST, OpenParenthesis)
FROM    (   SELECT  ST,
                    OpenParenthesis = CHARINDEX('(PRODUCED', ST)
            FROM    #M
        ) AS t;

Which gives:

ST                                                                  OpenParenthesis     CloseParenthesis
----------------------------------------------------------------------------------------------------------
please help me world  (produced... but needs to go)                 23                  51
please help me world Y (produced_this is extra extra extra long)    24                  64
please help me world Z (producedthis isshort)                       24                  45
please help me world K (produced                                    24                  0

Then you use these values for the position of the parenthesis inside the STUFF function to remove everything between the parenthesis:

SELECT  ST,
        NewST = CASE WHEN ST NOT LIKE '%(PRODUCED%)%' THEN ST
                    ELSE STUFF(ST, OpenParenthesis, CloseParenthesis - OpenParenthesis, '')
                END
FROM    (   SELECT  ST,
                    OpenParenthesis = CHARINDEX('(PRODUCED', ST) - 1,
                    CloseParenthesis = CHARINDEX(')', ST, CHARINDEX('(PRODUCED', ST)) + 1
            FROM    #M
        ) AS t;

Upvotes: 2

Related Questions