Remove everything between parenthesis

  • shahgols

    SSCrazy Eights

    Points: 9869

    Hi all,

    Assuming that I have the phrase "All I want (for Christmas) is better (TSQL) coding skills (this year)."

    And I want to remove the parenthesis and everything in between them, giving me "All I want is better coding skills."

    How would I do that WITHOUT using a function?

    Many thanks in advance!

  • pietlinden

    SSC Guru

    Points: 62607

    Use CHARINDEX() to find the position of the open/close parens, and then use SUBSTRING() to get the parts between them, and REPLACE() to swap that with an empty string?

    Sounds like an interview question.

  • pietlinden

    SSC Guru

    Points: 62607

    This worked... ugly, but it worked:

    DECLARE @string VARCHAR(200) = 'All I want (for Christmas) is better (TSQL) coding skills (this year).'
    --remove everything between parens
    --remove the parens
    DECLARE @StartPos INT, @EndPos INT;
    WHILE CHARINDEX(')',@string,1)>0
    BEGIN
     set @StartPos = CHARINDEX('(',@string,1);
     set @EndPos = 1+CHARINDEX(')',@String,1);
     PRINT 'StartPos = ' + CAST(@StartPos AS VARCHAR(2)) + ' EndPos = ' + CAST(@EndPos AS VARCHAR(2));
     SET @string = REPLACE(@string,SUBSTRING(@string,@StartPos,@EndPos-@StartPos),'');
    END;
    SET @string = REPLACE(@string,'  ',' ');
    PRINT @string;
  • Jonathan AC Roberts

    SSCoach

    Points: 16973

    shahgols wrote:

    Hi all,

    Assuming that I have the phrase "All I want (for Christmas) is better (TSQL) coding skills (this year)."

    And I want to remove the parenthesis and everything in between them, giving me "All I want is better coding skills."

    How would I do that WITHOUT using a function?

    Many thanks in advance!

    CHARINDEX, STUFF, PATINDEX CONVERT are all functions, so I don't think you can mean without a function.

    You could do it with a recursive CTE, but it still uses these functions

    ;WITH CTE AS
    (
    SELECT CONVERT(nvarchar(MAX),'All I want (for Christmas) is better (TSQL) coding skills (this year).') String,
    1 RowNum
    UNION ALL
    SELECT T.String, CTE.RowNum + 1
    FROM CTE
    CROSS APPLY (VALUES (CHARINDEX(' (',CTE.String), CHARINDEX(')',CTE.String))) P(StartB,EndB)
    CROSS APPLY (VALUES (STUFF(CTE.String,P.StartB,P.EndB-P.StartB+1,''))) T(String)
    WHERE PATINDEX('%(%)%',CTE.String)>0
    )
    SELECT TOP(1) String
    FROM CTE
    ORDER BY RowNum DESC

     

  • drew.allen

    SSC Guru

    Points: 76643

    Unfortunately, Jonathan's approach fails when you have nested parens.  This approach works even with nested parens.

    DECLARE @item VARCHAR(150) = 'All I want (for Christmas (or maybe my birthday) this year) is better (TSQL) coding skills (this year).';

    WITH Base AS ( SELECT n FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) ) t(n) )
    , Tally AS
    (
    SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) AS n
    FROM Base AS a
    CROSS JOIN Base AS b
    )
    , exploded AS
    (
    SELECT *
    , SUM(
    CASE
    WHEN s.cur_char = '(' THEN 1
    WHEN s.prev_char = ')' THEN -1
    ELSE 0
    END)
    OVER(ORDER BY t.n ROWS UNBOUNDED PRECEDING) AS paren_cnt
    FROM Tally AS t
    CROSS APPLY (VALUES(SUBSTRING(@item, n, 1), SUBSTRING(@item, n-1, 1))) s(cur_char, prev_char)
    WHERE t.n <= LEN(@item)
    )
    SELECT
    (
    SELECT
    CASE
    WHEN e.cur_char = ' ' AND LEAD(e.cur_char, 1, ' ') OVER(ORDER BY e.n) IN ('.', ' ') THEN ''
    ELSE e.cur_char
    END
    FROM exploded AS e
    WHERE e.paren_cnt = 0
    ORDER BY e.n
    FOR XML PATH, TYPE
    ).value('.', 'VARCHAR(150)')

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • shahgols

    SSCrazy Eights

    Points: 9869

    Thank you all, appreciate it.  I forgot to mention that the number of open/close parenthesis can vary.  I'll see if I can work off your solutions to make that happen.  But if you like the challenge, feel free to post a new solution 🙂

    Thanks again!

  • drew.allen

    SSC Guru

    Points: 76643

    shahgols wrote:

    Thank you all, appreciate it.  I forgot to mention that the number of open/close parenthesis can vary.  I'll see if I can work off your solutions to make that happen.  But if you like the challenge, feel free to post a new solution 🙂

    Thanks again!

    All of the suggested solutions work with any number of matched parens.  If you have unmatched parens, that is a different problem and you need to tell us how you want to handle that.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • shahgols

    SSCrazy Eights

    Points: 9869

    Just an update, looks like both solutions work with n number of parenthesis.  Brilliant!!!  Thank you so much!!!

Viewing 8 posts - 1 through 8 (of 8 total)

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