Remove everything between parenthesis
-
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!
-
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.
-
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; - 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 -
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 -
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!
- 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 -
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