Extracting a variable length string between braces

Question

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');

See if [this answer](http://stackoverflow.com/a/23001916/2055998) is helpful. — PM 77-1, Dec 07 '15 at 16:35
@Lamak usually near the end but better if I write something that assumes that `(produced%)` could be anywhere in the string — whytheq, Dec 07 '15 at 16:37

GarethD · Accepted Answer · 2017-10-27T07:35:13.273

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;

I'm saving my vote for when op answers the question I posted in a comment ;-) — Lamak, Dec 07 '15 at 16:37
@Lamak yes indeed ....apologies Gareth for the bad question....there could be text to the right of the phrase — whytheq, Dec 07 '15 at 16:38
One pitfall here is that if you have multiple occurrences of `(produced ....)` in the same string, only the first one would be removed, however, a solution for multiple replacements gets fairly complicated, so for now I will assume this is not going to be an issue. — GarethD, Dec 07 '15 at 16:49
@GarethD - only ever one occurance - thanks for your time spent on this answer. — whytheq, Dec 08 '15 at 08:43

Extracting a variable length string between braces

1 Answers1