querry get data in double codes in string

Question

querry get data in double codes in string

mpradeep23

SSCommitted

Points: 1868
More actions
February 9, 2014 at 7:05 am

#396239

Hi all
i have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
i want to get only data in double codes in using sql querry
thanks,
Pradeep

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

mpradeep23 (2/9/2014)
Hi all
i have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
i want to get only data in double codes in using sql querry
thanks,
Pradeep

If given the data above, precisely what do you want for an output?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

TheSQLGuru (2/9/2014)
Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?

THAT's JSON? Shoot... that's just as bad or worse than attribute based XML. We need a few more "standards" for passing simple data. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Jeff Moden (2/9/2014)
TheSQLGuru (2/9/2014)
Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?
THAT's JSON? Shoot... that's just as bad or worse than attribute based XML. We need a few more "standards" for passing simple data. 😀

I personally feel that JSON is MUCH worse than XML (and I frickin' HATE XML, so that should tell you something), but alas JSON has taken over and we are just going to have to learn to make the best of it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

TheSQLGuru (2/9/2014)
Jeff Moden (2/9/2014)
TheSQLGuru (2/9/2014)
Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?
THAT's JSON? Shoot... that's just as bad or worse than attribute based XML. We need a few more "standards" for passing simple data. 😀
I personally feel that JSON is MUCH worse than XML (and I frickin' HATE XML, so that should tell you something), but alas JSON has taken over and we are just going to have to learn to make the best of it.

Ah, my friend. I've found a kindred spirit. You've made my day and maybe the whole bloody week! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Jeff Moden (2/9/2014)
TheSQLGuru (2/9/2014)
Jeff Moden (2/9/2014)
TheSQLGuru (2/9/2014)
Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?
THAT's JSON? Shoot... that's just as bad or worse than attribute based XML. We need a few more "standards" for passing simple data. 😀
I personally feel that JSON is MUCH worse than XML (and I frickin' HATE XML, so that should tell you something), but alas JSON has taken over and we are just going to have to learn to make the best of it.
Ah, my friend. I've found a kindred spirit. You've made my day and maybe the whole bloody week! 🙂

Make that three against JSON.

Had a project once where they required us to convert JSON to XML, then normalize and import into the database, then believe it or not - reconvert the normalized data into a JSON string to also be stored.

They didn't like that we shortcuted and just passed the JSON string to the database rather than converting it from normalized back to JSON. But they learned to accept it (they being those who came up with the stinking requirement).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mpradeep23 SSCommitted Points: 1868 More actions · Answer 7

hi

i need out put as

type Start_Date End_Date

Factory Warranty 2012-12-21 2014-01-19

thanks,

pradeep

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

mpradeep23 (2/10/2014)
hi
i need out put as
type Start_Date End_Date
Factory Warranty 2012-12-21 2014-01-19
thanks,
pradeep

Do you need to actually STORE this data inside SQL Server first (the original record) and then later you want to be able to create the output you list above with a SELECT statement? And if so, does that output eventually get converted into JSON format or does it get consumed in Tabular Datastream format? We need you to provide us with more details so we can help you more effectively!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mpradeep23 SSCommitted Points: 1868 More actions · Answer 9

Hi

i have table called t1

with column name offers with data as

"entitlementwrapper" : [ {

"Type" : "Factory Warranty",

"Date_Type" : "Ship date",

"Status" : "Active",

"Start_Date" : "2012-12-21",

"End_Date" : "2014-01-19",

"Days_Left" : "116",

"Term" : "13",

"Description" : "Wty: HP HW Replacement Support",

"IsTrusted" : "Y",

"Transaction_ID" : "4644780453"

},

i want to get data to store in other table

1.type

2.start_date

3.end_date

i need queery to load data into this table

thanks,

Pradeep

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

mpradeep23 (2/10/2014)
Hi
i have table called t1
with column name offers with data as
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
},
i want to get data to store in other table
1.type
2.start_date
3.end_date
i need queery to load data into this table
thanks,
Pradeep

So your table is storing the JSON in some form of character column. Got it.

You cannot DIRECTLY do what you want. You cannot write a SELECT statement against JSON data in SQL Server. You need to first use some mechanism to PARSE your JSON into some type of tabular format (I recommend SQL CLR for that personally). You could write TSQL to do the parsing but TSQL is not very good at that (if you pursue it, look up SUBSTRING and CHARINDEX). You can also likely (not certain) do some XML stuff to work the parsing too. I foresee numerous complexities (and thus time involvement) in creating rigorous parsing code here, so I consider this beyond the realm of a forum post for assistance. Perhaps someone else will take up the charge here.

Best of luck!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

sqldriver SSChampion Points: 10440 More actions · Answer 11

TheSQLGuru (2/9/2014)
Jeff Moden (2/9/2014)
TheSQLGuru (2/9/2014)
Please provide several sample sets of data and the corresponding table output you expect (with column data types). That's JSON, yes?
THAT's JSON? Shoot... that's just as bad or worse than attribute based XML. We need a few more "standards" for passing simple data. 😀
I personally feel that JSON is MUCH worse than XML (and I frickin' HATE XML, so that should tell you something), but alas JSON has taken over and we are just going to have to learn to make the best of it.

People who like JSON: CS freshmen pretty printing address books in Java.

People who hate JSON: Everyone else.