How to get 2 sub strings as 2 columns from single column of table

Question

[{"key":"Mobile","value":"9100617634"},{"key":"Email","value":"[email protected]"}]

The above one represents the value in one column of a table. I want 2 columns named as mobile and email where those having values as 9000617634,balajirao@ziraff, respectively . How to get in sql server.

see the below one.

mobile             Email
------             ------------
9100617634         [email protected]

Answer 1

SELECT case when [MOBILE/PHONE] like '[a-z]%' then null else [MOBILE/PHONE] end as [MOBILE/PHONE], case when ISNUMERIC(email)=0 then EMAIL end as EMAIL FROM ( select REPLACE(SUBSTRING(RIGHT(right(left(AC.Communication,charindex('}',AC.Communication)),len(left(AC.Communication,charindex('}',AC.Communication))) - charindex(':',left(AC.Communication,charindex('}',AC.Communication)))),LEN(right(left(AC.Communication,charindex('}',AC.Communication)),len(left(AC.Communication,charindex('}',AC.Communication))) - charindex(':',left(AC.Communication,charindex('}',AC.Communication)))))-CHARINDEX(':',right(left(AC.Communication,charindex('}',AC.Communication)),len(left(AC.Communication,charindex('}',AC.Communication))) - charindex(':',left(AC.Communication,charindex('}',AC.Communication)))))),2,CHARINDEX(']',AC.Communication)),'"}','') AS [MOBILE/PHONE], REPLACE(REPLACE(LEFT(right(AC.Communication,charindex(':',reverse(AC.Communication))),LEN(right(AC.Communication,charindex(':',reverse(AC.Communication))))-charindex('}',reverse(right(AC.Communication,charindex(':',reverse(AC.Communication)))))),':"',' '),'"','') AS EMAIL

from DimAccountContact as AC

Answer 2

U can Use Pivot Function to convert the row values to column, Below is the Static Code for implementation

SELECT * FROM(
   SELECT
     columns_name
   FROM
      table_name
     ) M
   PIVOT (MAX(Key) FOR table_name IN (mobile ,Email))AS  P ;

Answer 3

Well that was fun doing it the hard way :)

--initial string
declare @s1 varchar(1000) = (select '[{"key":"Mobile","value":"9100617634"},{"key":"Email","value":"[email protected]"}]')

--selecting first part and second part of string
declare @mobile varchar(100) = (select right(left(@s1,charindex('}',@s1)-1),len(left(@s1,charindex('}',@s1))) - charindex(':',left(@s1,charindex('}',@s1)))-1))
declare @mail varchar(100) = (select right(@s1,charindex(':',reverse(@s1))-1))

--getting rid of extra characters
set @mobile = (right(@mobile, len(@mobile) - charindex(':',@mobile)))
set @mail = (left(@mail, len(@mail) - charindex('}',reverse(@mail))))


--getting rid of double quotes
set @mobile = replace(@mobile,'"','')
set @mail = replace(@mail,'"','')

--selecting data
select 
@mobile as Mobile,
@mail as Mail

Result is as following:

Mobile Mail

9100617634 [email protected]