Remove everything between parenthesis

Question

Remove everything between parenthesis

shahgols

SSCrazy Eights

Points: 9874
More actions
October 3, 2019 at 11:22 pm

#3685888

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!

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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: 63503 More actions · Answer 2

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: 18354 More actions · Answer 3

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: 76985 More actions · Answer 4

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: 9874 More actions · Answer 5

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: 76985 More actions · Answer 6

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: 9874 More actions · Answer 7

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