CODEWITHSUNDEEP
sqlsql-servert-sql
Anna
Anna

Reputation: 464

Removing closing bracket in SQL

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

Answers (2)

Alan Burstein
Alan Burstein

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

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You could try the following find and replace, in regex mode, from SSMS:

Find:    \[(.*?)\]
Replace: $1

Demo

Upvotes: 1

Related Questions