King of Microsoft King of Microsoft - 1 month ago 6
SQL Question

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

I'm using SQL Server 2014 ....

Select testvalue from testtable


returns

[000001][xXCEWkC+WDhe7EYo6feDmQ==]mnjQ3UkMjb1swK1wCTT75Q==


How can I split this value into 2 different values?


  1. Value in 2nd brackets

  2. Value after 2nd set of brackets


Answer

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
;

MySQL

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

from    testtable
;
Comments