Wednesday, August 19, 2015

Stuff number inside square brackets (Ex: [123]) from a string in SQL Server

When you Search square brackets from a string... you can't get it if you write query
like '%[ ]%'

Here my goal is to remove the number with square brackets but not the character  inside square brackets.

To achieve this goal you have to follow the code given below.

DECLARE @TEXT VARCHAR(MAX),@INITIAL INT,@FINAL INT,@PATSTART 

INT,@PATEND INT;

SET @TEXT='ASFASFA [123] ASF [ABC] AFAFS [567] 123 AFASFA [123] AFASF PRITAM[12]DAS'

SET @INITIAL=1;

SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT)

WHILE (@INITIAL>0)
BEGIN
       SET @PATSTART=@INITIAL-1;
       SET @PATEND=CHARINDEX(']',@TEXT,@INITIAL);
       SET @TEXT=STUFF(@TEXT,@PATSTART,@PATEND+1-@PATSTART,'');
       SET @INITIAL=PATINDEX('%[[][0-9]%',@TEXT);
END

SELECT @TEXT;


No comments:

Post a Comment