Reputation: 464
How do I get rid of a closing bracket in the same line, as all [QuestionID]
have all opening and closing brackets?
,SUBSTRING(t.[QuestionID],12,200) AS [QuestionID2]
Upvotes: 0
Views: 740
Reputation: 7928
This removes all right brackets but does not solve the problem:
--==== Remove ALL right brackets (e.g. "closing brackets")
DECLARE @t TABLE (QId INT IDENTITY, question VARCHAR(1000));
INSERT @t VALUES ('blah blah [1234XX]'),('!! [XX555] !!!'),('[334466ACB]');
SELECT t.*, NewString = REPLACE(t.Question,']','')
FROM @t AS t;
If we only want closing brackets removed when there is an opening bracket then you can break out APPLY
like this:
--==== Remove closing brackets that have an opening bracket
DECLARE @t TABLE (QId INT IDENTITY, question VARCHAR(1000));
INSERT @t VALUES ('blah blah [1234XX]'),('!! [XX555] !!!'),('[334466ACB]'),
('...]]...[9922ttt]...321')
SELECT t.question, NewString = STUFF(t.question,f2.Pos,1,'')
FROM @t AS t
CROSS APPLY (VALUES(CHARINDEX('[',t.question)+1)) AS f(Pos)
CROSS APPLY (VALUES(CHARINDEX(']',t.question,f.Pos))) AS f2(Pos);
Returns:
question NewString
----------------------------- ------------------------------
blah blah [1234XX] blah blah [1234XX
!! [XX555] !!! !! [XX555 !!!
[334466ACB] [334466ACB
...]]...[9922ttt]...321 ...]]...[9922ttt...321
This preserves closing brackets without a preceding opening bracket.
Now let's say we have multiple opening/closing brackets. For this you could use ngrams8k like so:
DECLARE @t TABLE (QId INT IDENTITY, question VARCHAR(1000));
INSERT @t VALUES ('blah blah [1234XX]'),('!! [XX555] !!!'),
('[334466ACB]...[567]...[899]???'),(']][9922ttt]...[321]');
SELECT ng.QId, NewString = STRING_AGG(IIF(ng.Pos>0 AND ng.Token=']','',ng.Token),'')
WITHIN GROUP (ORDER BY ng.Position)
FROM
(
SELECT t.QId, ng.Token, ng.Position, Pos = SUM(CASE ng.Token WHEN '[' THEN 1 END)
OVER (PARTITION BY t.QId ORDER BY ng.Position)
FROM @t AS t
CROSS APPLY dbo.NGrams8k(t.Question,1) AS ng
) AS ng
GROUP BY ng.QId;
Returns:
QId NewString
------ -----------------------------------
1 blah blah [1234XX
2 !! [XX555 !!!
3 [334466ACB...[567...[899???
4 ]][9922ttt...[321
You haven't been 100% clear about what the data looks like so it's possible some tweaks are required but they won't be difficult.
Upvotes: 0
Reputation: 522762
You could try the following find and replace, in regex mode, from SSMS:
Find: \[(.*?)\]
Replace: $1
Upvotes: 1