Piyush Bhardwaj Piyush Bhardwaj -4 years ago 132
SQL Question

SQL- Only keep square brackets and data inside those

I was looking for a way to dynamically parse a string such as this : A[1]/B2[2]/C_D[1] and output should be [1][2][1] .

Answer Source

Simply using while loop.

declare @str varchar(max), @newStr varchar(max), @orgStr varchar(max)
set @orgStr = 'A[1]/B2[2]/C_D[1]'
set @newStr = ''
set @Str = @orgStr
while (1=1)
begin
    if (CHARINDEX('[',@str) <> 0)
        begin
            set @newStr = @newStr + SUBSTRING(@str,CHARINDEX('[',@str), CHARINDEX(']',@str) - CHARINDEX('[',@str)+1)
            set @str = STUFF(@str, CHARINDEX('[',@str), CHARINDEX(']',@str) - CHARINDEX('[',@str)+1, '')
        end
    else
        break
end
select @orgStr as input, @newStr as result
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download