4

I have a Json string. I Want to get value from that Json string.

This is my json string {"latitude":"22.5712854"},{"longitude":"88.4266847"}

I want only latitude and longitude from this, using TSQL query.

Hardik Mishra
  • 14,779
  • 9
  • 61
  • 96
Pinaki Mukherjee
  • 185
  • 4
  • 10
  • 3
    This could help you: https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/ – Cristi Pufu Jun 04 '13 at 10:12
  • TSQL can't parse JSON natively and it's a very weak language for text parsing and handling in general (although you could parse the string you've shown using only `SUBSTRING` and `CHARINDEX`). It will almost certainly be easier to do this using code outside the database or possibly a CLR procedure; that way you can use a language that has JSON support or at least good text processing functions. – Pondlife Jun 04 '13 at 15:48
  • 2
    possible duplicate of [Parse JSON in TSQL](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) – AndrewC Jun 04 '13 at 23:40
  • Check the [question](http://stackoverflow.com/questions/2867501/parse-json-in-tsql) duplicated here as flagged by AndrewC. Its answers are more thorough and it mentions the native `JSON` support in SQL Server 2016. – Frédéric Feb 26 '16 at 12:30

2 Answers2

5

There is no native way to parse JSON in TSQL. But Phil Factor created his own implementation of paring JSON in SQL function. More about it on simple talk blog in article: Consuming JSON Strings in SQL Server

Aslo Ric Vander Ark created his own function but I did not tested it. You can read more on article: A Function to Split JSON Data

Piotr Stapp
  • 19,392
  • 11
  • 68
  • 116
  • 2
    "There is no native way to parse JSON in TSQL. But Phil Factor created his own implementation [to parse JSON in TSQL]" <-- Contradiction at its best. Still, thanks for the link on how to parse JSON in TSQL. :) – rainabba Oct 26 '13 at 00:26
  • A custom implementation, as listed is different to a native (i.e. built in) way. – Runonthespot Jul 16 '14 at 12:58
0

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

select 
    dbo.JsonDecimal(my_column, 'latitude') as latitude,
    dbo.JsonDecimal(my_column, 'longitude') as longitude
from my_table

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

joshuahealy
  • 3,529
  • 22
  • 29