King of Microsoft King of Microsoft - 3 months ago 22
SQL Question

SQL Server String Manipulation, splitting column value into 2 seperate values

I'm using SQL Server 2014 ....

Select testvalue from testtable



How can I split this value into 2 different values?

  1. Value in 2nd brackets

  2. Value after 2nd set of brackets


SQL Server

select      right(testvalue,charindex(']',reverse(testvalue))-1)                                                            as col_1_option_a
           ,right(testvalue,len(testvalue)-patindex('%][^[]%',testvalue))                                                   as col_1_option_b
           ,right(left(testvalue,patindex('%][^[]%',testvalue)-1),patindex('%][^[]%',testvalue)-charindex(']',testvalue)-2) as col_2

from        testtable


select  substring_index(substring_index(testvalue ,'[',-1),']',1)
       ,substring_index(testvalue ,']',-1)

from    testtable