Split string into different column with sql

Question

How can I split this particular string in the column to three different column

sample:

Column_A
----------
{"nationalCode":"1234567893","username":"123","ip":"::1"}
{"nationalCode":"1234567892","username":"test","ip":"172.30.10.11"}
{"nationalCode":"1234567890"}
{"nationalCode":"1234567891","username":"test"}

should be

nationalcode|username|Ip
---------------------------
1234567893  |123     |::1 
1234567892  |test    |172.30.10.11
1234567890  |null    |null
1234567891  |test    |null

what I have tried so far for just one of the field is(I have no Idea how to solve it)

 select  SUBSTRING(REPLACE(SUBSTRING(ActionInput,CHARINDEX('nationalCode',ActionInput)+len('nationalCode":"'),CHARINDEX(',',ActionInput)),'""',''),0,11) as national1,
         REPLACE(SUBSTRING(ActionInput,CHARINDEX('username',ActionInput)+len('username":"'),CHARINDEX(',',ActionInput)),'"}','') as a

        from LogTBL

Is it always a maximum of those three values and do they ALWAYS come in exactly the same order? Ideally you should never store data like this. This is even worse than xml and violates 1NF. — Sean Lange
– Sean Lange, Commented May 3, 2017 at 18:23

TriV · Accepted Answer · 2017-05-03 18:33:46Z

In this case, you could use Replace to change Column_A into XML and get expected data by VALUE()

DECLARE @SampleData AS TABLE
(
    Column_A varchar(max)
)

INSERT INTO @SampleData
VALUES 
('{"nationalCode":"1234567893","username":"123","ip":"::1"}'),
('{"nationalCode":"1234567892","username":"test","ip":"172.30.10.11"}'),
('{"nationalCode":"1234567890"}'),
('{"nationalCode":"1234567891","username":"test"}')

;WITH temp AS
(
    SELECT CAST(REPLACE(replace(replace(replace(sd.Column_A, '":"', '="'),
                    '{"', '<x '),'}','></x>' ), '","','" ') AS xml) AS XmlValue
    FROM @SampleData sd
)
SELECT  t.XmlValue.value('(/x/@nationalCode)[1]', 'varchar(30)') AS nationalCode,
        t.XmlValue.value('(/x/@username)[1]', 'varchar(30)') AS username,
        t.XmlValue.value('(/x/@ip)[1]', 'varchar(30)') AS ip
FROM temp t

Demo link: http://rextester.com/YEPUI94059

Collectives™ on Stack Overflow

Split string into different column with sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related