1

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.

haim770
  • 48,394
  • 7
  • 105
  • 133
Pradip_145
  • 11
  • 1
  • 4
  • 3
    Don't store multiple values like this in a single column - this **violates** the **first normal form** of database design – marc_s Nov 20 '14 at 08:59
  • so your address column contain like this , right ? line1:Nav Place Road,line2:Nyork City,line3:USA 34576 – Krish KvR Nov 20 '14 at 09:01
  • 1
    See https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/ – haim770 Nov 20 '14 at 09:02
  • @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 Nov 20 '14 at 09:03
  • Stored as XML would be better; at least you could query it then. – DeanOC Nov 20 '14 at 09:04
  • @marc_s What would be the best way to store keywords for a product/article in a relational DB? Especially if the number of keywords you can input is not limited? – Radu Gheorghiu Nov 20 '14 at 09:29
  • @RaduGheorghiu: add a separate `keywords` table that contains keywords (any number of them) with a link to the `Product` or `Article` table. Just a simple, basic, nicely relational 1:n relationship..... – marc_s Nov 20 '14 at 11:49
  • @marc_s Thank you, I have to get my knowledge of RDBs back on track. – Radu Gheorghiu Nov 20 '14 at 12:19
  • possible duplicate of [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) – bummi Jun 25 '15 at 07:09

1 Answers1

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 :)

joshuahealy
  • 3,529
  • 22
  • 29