Split JSON data in SQL Server column

Question

I have a table in SQL Server which has an address column of string datatype.

Address values like

{"line1":"Nav Place Road","line2":"Nyork City","line3":"USA 34576"}

I want to get result in a separate column line1, line2, line3, line4 with select query.

I try with split function, but I can't get proper result.

Don't store multiple values like this in a single column - this violates the first normal form of database design — marc_s
– marc_s, Commented Nov 20, 2014 at 8:59
so your address column contain like this , right ? line1:Nav Place Road,line2:Nyork City,line3:USA 34576 — Krish KvR
– Krish KvR, Commented Nov 20, 2014 at 9:01
@marc_s Or to put it another way, it's fine to store multiple values in a column like this. But don't expect access to them from a SQL query; only from your client application. — Tim Rogers
– Tim Rogers, Commented Nov 20, 2014 at 9:03
Stored as XML would be better; at least you could query it then. — DeanOC
– DeanOC, Commented Nov 20, 2014 at 9:04

joshuahealy · Accepted Answer · 2020-02-18 19:46:31Z

2

You could use JSON Select which providers several functions for extracting different datatypes from JSON. For your example you could do something like this:

select 
    dbo.JsonNVarChar450(address, 'line1') as line1,
    dbo.JsonNVarChar450(address, 'line2') as line2,
    dbo.JsonNVarChar450(address, 'line3') as line3
from your_table

DISCLOSURE: I am the author of JSON Select, and as such have an interest in you using it :)

edited Feb 18, 2020 at 19:46

answered Jun 23, 2015 at 7:09

joshuahealy

3,57924 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Split JSON data in SQL Server column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related