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

Question

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?

Value in 2nd brackets
Value after 2nd set of brackets

Which DBMS are you using?

user330315
– user330315

2016-10-26 15:58:05 +00:00
Commented Oct 26, 2016 at 15:58 — user330315
– user330315, Commented Oct 26, 2016 at 15:58

David דודו Markovitz · Accepted Answer · 2016-10-26 18:52:15Z

1

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
;

edited Oct 26, 2016 at 18:52

answered Oct 26, 2016 at 16:34

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

King of Microsoft Over a year ago

Thank you but do you know how to accomplish this in SQL Server?

Michael Becerra · Accepted Answer · 2016-10-26 16:17:31Z

0

If you are using MySQL, you can use substring and locate:

SELECT SUBSTR(testvalue,
1,LOCATE("]",testvalue,LOCATE("]",testvalue)+1)) AS column1,
SUBSTR(testvalue,
LOCATE("]",testvalue,LOCATE("]",testvalue)+1)+1) AS column2 FROM testtable

answered Oct 26, 2016 at 16:17

Michael Becerra

4111 gold badge4 silver badges17 bronze badges

1 Comment

King of Microsoft Over a year ago

Thank you but do you know how to accomplish this in SQL Server?

Collectives™ on Stack Overflow

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

2 Answers 2

SQL Server

MySQL

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

SQL Server

MySQL

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related