how to fetch the text written in square brackets[] through a sql server query

Question

How to fetch the text written in square brackets[] through a sql server query

Input:

test1/test2/test3[ab]/test4[c]

Output:

ab  
c

Answer 1

Example

Declare @YourTable table (SomeCol varchar(500))
Insert Into @YourTable values
('test1/test2/test3[ab]/test4[c]')


Select A.*
      ,NewValue = left(value,charindex(']',Value)-1)
 From  @YourTable A
 Cross Apply string_split(SomeCol,'[') B
 Where B.value like '%]%'

Returns

SomeCol                         NewValue
test1/test2/test3[ab]/test4[c]  ab
test1/test2/test3[ab]/test4[c]  c

(Over-thunk the original answer)

Answer 2

You use this following logic-

DECLARE @Test VARCHAR(MAX) = 'test1/test2/test3[ab]/test4[c]'
DECLARE @TempTab TABLE (
    Val VARCHAR(100)
)

DECLARE @Loop INT = 1
DECLARE @St INT = 0
DECLARE @End INT = 0

WHILE @Loop <= (SELECT LEN(@Test))

BEGIN
   IF SUBSTRING(@Test, @Loop, 1) = CASE WHEN @St = 0 THEN '[' ELSE ']' END
   BEGIN
       IF @St = 0
           SET @St = @Loop + 1
       ELSE 
           SET @End = @Loop
   END

   IF @St <> 0 AND @End <> 0
   BEGIN
       INSERT INTO @TempTab(Val)
       SELECT  SUBSTRING (@Test,@St,@End-@St)

       SET @St = 0
       SET @End = 0
   END

    SET @Loop = @Loop +1
END

SELECT * FROM @TempTab