Help - How to Efficiently Split a Column of Data

Question

Help - How to Efficiently Split a Column of Data

RMason - eLIMBS, LLC

Mr or Mrs. 500

Points: 504
More actions
March 9, 2017 at 8:39 am

#335781

I have a table which contains the following information (as well as plenty other columns of information):
{"23953":{"userRate29441":"160.00","userRate58591":"160.00","userRate57694":"15.00","userRate97056":"90.00","userRate86976":"180.00","userRate29397":"250.00","userRate29396":"17.20","userRate95853":"180.00","userRate126595":"180.00","userRate48853":"15.00","userRate29439":"15.00","userRate86847":"180.00","userRate29443":"15.00","billingCurrency":"USD","userRate29437":"15.00","userRate82065":"160.00","userRate29445":"15.00","userRate29409":"15.00","userRate29444":"15.00","userRate29447":"15.00","defaultRate":"180.00"}} {"23970":{"userRate29848":"160.00","userRate29441":"160.00","userRate58591":"160.00","files-listMode":"list","userRate57694":"160.00","userRate30031":"160.00","userRate29840":"160.00","userRate29397":"160.00","userRate29396":"160.00","userRate48853":"90.00","billingCurrency":"USD","userRate29437":"160.00","project-notebooks-sortmode":"bydate","userRate29409":"160.00","userRate29433":"0.00","userRate50858":"160.00","defaultRate":"160.00","userRate29449":"160.00","userRate29500":"0"}} {"23973":{"userRate29441":"160","files-listMode":"list","userRate48853":"160","files-sortMode":"bydate","userRate29437":"160","userRate29397":"160","userRate29409":"160","project-notebooks-sortmode":"bycat","defaultRate":"160"}} {"23981":{"userRate29441":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate97482":"160.00","userRate95853":"160.00","userRate86847":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate30289":"160.00","project-notebooks-sortmode":"bydate","userRate29409":"160.00","userRate29449":"160.00","defaultRate":"160.00"}} {"24139":{"userRate29441":"160.00","userRate58591":"160.00","userRate41810":"160.00","userRate57694":"160.00","userRate29397":"160.00","userRate29396":"160","userRate41809":"160.00","userRate48853":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate29409":"160.00","project-notebooks-sortmode":"byname","defaultRate":"160.00"}} {"24303":{"files-listMode":"list"}} {"28722":{"userRate29441":"160.00","userRate58591":"0.00","files-listMode":"grid","userRate48853":"160.00","userRate57694":"0.00","billingCurrency":"USD","userRate29437":"0.00","lastNoteBkCat":"27898","userRate29397":"160.00","userRate29409":"0.00","userRate36761":"0.00","defaultRate":"0.00"}} {"32251":{"userRate29441":"160.00","userRate58591":"160.00","userRate45081":"160.00","userRate66073":"160.00","userRate57694":"160.00","lastNoteBkCat":"6546","userRate86976":"160.00","userRate29397":"160.00","userRate95853":"160.00","userRate48853":"160.00","billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29437":"160.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"31729"}} {"38469":{"userRate29441":"160.00","userRate58591":"0.00","files-listMode":"list","userRate48853":"160.00","userRate57694":"0.00","billingCurrency":"USD","lastNoteBkCat":"9037","userRate29437":"0.00","userRate29397":"160.00","userRate29409":"0.00","defaultRate":"0.00"}} {"43808":{"userRate29441":"160","userRate48853":"160","userRate57694":"160","billingCurrency":"USD","userRate29437":"160","userRate29397":"160","userRate29409":"160","defaultRate":"160"}} {"49434":{"userRate29441":"45.00","userRate48853":"45.00","userRate57695":"45.00","userRate57694":"45.00","userRate29437":"45.00","userRate29397":"45.00","userRate29409":"45.00","defaultRate":"45.00"}} {"58855":{"userRate29441":"160.00","userRate58591":"160.00","userRate48853":"160.00","userRate57694":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"59151":{"userRate29441":"160.00","userRate58591":"160.00","userRate57694":"120.00","lastNoteBkCat":"24215","userRate86976":"160.00","userRate29397":"240.00","userRate75047":"160.00","userRate95853":"160.00","userRate48853":"240.00","billingCurrency":"USD","userRate82065":"160.00","userRate29437":"0.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"53222"}} {"60802":{"billingCurrency":"USD","userRate82065":"180.00","userRate86976":"180.00","userRate29397":"250.00","userRate29409":"0.00","defaultRate":"160.00","userRate95853":"180.00"}} NULL {"68135":{"userRate29441":"160.00","userRate58591":"0.00","userRate57694":"0.00","lastNoteBkCat":"16117","userRate29397":"0.00","userRate71587":"0.00","userRate48853":"160.00","files-sortMode":"byname","billingCurrency":"USD","userRate29437":"0.00","userRate29409":"0.00","defaultRate":"0.00","lastFileCat":"46423"}} {"72640":{"userRate29441":"0.00","userRate58591":"160.00","userRate48853":"0.00","userRate57694":"0.00","billingCurrency":"USD","userRate82065":"160.00","userRate29437":"0.00","userRate29397":"160.00","userRate29409":"0.00","defaultRate":"160.00"}} {"74618":{"userRate29441":"100.00","userRate58591":"0.00","userRate48853":"0.00","userRate57694":"0.00","billingCurrency":"USD","userRate29437":"0.00","userRate29397":"0.00","userRate29409":"0.00","defaultRate":"0.00"}} {"78635":{"userRate29441":"0.00","userRate58591":"0.00","userRate48853":"0.00","userRate57694":"0.00","billingCurrency":"USD","userRate82065":"160.00","userRate29437":"0.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"88280":{"userRate29441":"160.00","userRate58591":"160.00","userRate48853":"160.00","userRate57694":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"88414":{"userRate29441":"99.00","userRate58591":"0.00","userRate57694":"0.00","userRate105405":"99.00","userRate97056":"99.00","userRate86976":"99.00","userRate29397":"99.00","userRate97482":"99.00","userRate95853":"99.00","userRate48853":"0.00","userRate86847":"99.00","billingCurrency":"USD","userRate82065":"99.00","userRate29437":"0.00","userRate29409":"99.00","defaultRate":"99.00"}} {"102167":{"billingCurrency":"USD"}} {"104857":{"billingCurrency":"USD"}} {"107601":{"userRate58591":"160.00","userRate57694":"160.00","userRate84842":"160.00","lastNoteBkCat":"37414","userRate29397":"160.00","userRate84847":"160.00","userRate84845":"160.00","userRate84846":"160.00","userRate84844":"160.00","billingCurrency":"USD","files-sortMode":"bydate","userRate82065":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"108471":{"userRate58591":"160.00","userRate57694":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"110102":{"userRate86847":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"114972":{"userRate86847":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"121219":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"123134":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"124749":{"userRate86847":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"126543":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"130338":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"132207":{"lastMsgCat":"0","userRate97056":"0.00","userRate86976":"160.00","userRate29397":"250.00","userRate94885":"0.00","userRate94884":"0.00","userRate97482":"160.00","userRate95853":"160.00","userRate95547":"0.00","billingCurrency":"USD","userRate86847":"160.00","userRate96581":"0.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"299329"}} {"132219":{"billingCurrency":"USD","userRate86847":"160.00","lastNoteBkCat":"59340","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"347605"}} {"133635":{"userRate86847":"160.00","billingCurrency":"USD","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"133862":{"userRate86847":"160.00","billingCurrency":"USD","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}} {"134434":{"userRate86847":"160.00","billingCurrency":"USD","userRate97056":"160.00","userRate29397":"160.00","userRate86976":"180.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} NULL {"136004":{"billingCurrency":"USD","userRate86847":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate97056":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"136110":{"userRate86847":"160.00","billingCurrency":"USD","userRate86976":"160.00","userRate29397":"160.00","userRate97056":"160.00","userRate29409":"160.00","userRate97482":"160.00","defaultRate":"160.00","lastFileCat":"352588","userRate95853":"160.00"}} {"137406":{"billingCurrency":"USD","userRate86847":"160.00","lastNoteBkCat":"52881","userRate86976":"160.00","userRate29397":"250.00","userRate97056":"80.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00","lastFileCat":"290254"}} {"138360":{"lastFileCat":"322263"}} {"138578":{"billingCurrency":"USD"}} {"140947":{"userRate104559":"0.00","userRate104558":"0.00","userRate86847":"160.00","billingCurrency":"USD","userRate86976":"160.00","userRate29397":"250.00","userRate29409":"0.00","userRate97482":"0.00","defaultRate":"160.00","userRate95853":"160.00","lastFileCat":"342910"}} {"141254":{"billingCurrency":"USD","userRate86847":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","userRate97482":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"148291":{"billingCurrency":"USD"}} {"150297":{"userRate105709":"125.00","lastNoteBkCat":"73929","userRate105405":"0.00","userRate29397":"250.00","userRate86976":"180.00","userRate105158":"180.00","userRate105157":"180.00","userRate97482":"0.00","userRate95853":"180.00","userRate126595":"180.00","billingCurrency":"USD","userRate86847":"160.00","userRate29409":"180.00","userRate98814":"180.00","defaultRate":"180.00","lastFileCat":"437233"}} {"151062":{"billingCurrency":"USD","userRate86847":"180.00","userRate105405":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate98814":"180.00","userRate29409":"180.00","userRate97482":"0.00","defaultRate":"180.00","userRate95853":"180.00"}} NULL {"154339":{"billingCurrency":"USD","userRate86847":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate98814":"160.00","userRate29409":"160.00","userRate97482":"160.00","defaultRate":"160.00","userRate95853":"160.00","lastFileCat":"371676"}} {"154364":{"billingCurrency":"USD","userRate86847":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","userRate98814":"160.00","userRate97482":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"156206":{"userRate86847":"160.00","billingCurrency":"USD","userRate29397":"160.00","userRate86976":"160.00","userRate98814":"160.00","userRate29409":"160.00","userRate97482":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} NULL NULL {"158633":{"billingCurrency":"USD","userRate86847":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate98814":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"159502":{"userRate105709":"125.00","userRate105708":"80.00","lastNoteBkCat":"74279","userRate86976":"160.00","userRate29397":"250.00","userRate105405":"160.00","userRate95853":"160.00","userRate86847":"160.00","billingCurrency":"USD","userRate29409":"160.00","userRate98814":"160.00","defaultRate":"160.00","lastFileCat":"401338"}} NULL {"159824":{"billingCurrency":"USD","userRate86847":"180.00","userRate105405":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate98814":"160.00","userRate29409":"160.00","defaultRate":"180.00","userRate95853":"180.00"}} {"161369":{"userRate86847":"160.00","billingCurrency":"USD","userRate86976":"160.00","userRate29397":"160.00","userRate105405":"160.00","userRate97056":"80.00","userRate29409":"160.00","userRate98814":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"161388":{"userRate86847":"160.00","billingCurrency":"USD","userRate86976":"0.00","userRate29397":"250.00","userRate29409":"0.00","userRate98814":"0.00","defaultRate":"0.00","userRate95853":"0.00"}} {"161877":{"billingCurrency":"USD","userRate86847":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate98814":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"162583":{"billingCurrency":"USD","lastFileCat":"419370"}} NULL {"163190":{"userRate86847":"160.00","billingCurrency":"USD","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","userRate98814":"160.00","defaultRate":"160.00","userRate95853":"160.00"}} {"163192":{"billingCurrency":"USD","userRate86847":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate105405":"180.00","userRate98814":"160.00","userRate29409":"160.00","defaultRate":"180.00","userRate95853":"180.00"}} {"163670":{"userRate105708":"90.00","userRate86847":"180.00","billingCurrency":"USD","userRate86976":"180.00","userRate29397":"250.00","userRate105405":"180.00","userRate29409":"180.00","userRate98814":"180.00","defaultRate":"180","userRate95853":"180.00"}} {"164664":{"billingCurrency":"USD"}} {"165377":{"userRate86847":"160.00","billingCurrency":"USD","userRate86976":"99.00","userRate29397":"160.00","userRate105405":"99.00","userRate29409":"160.00","userRate98814":"160.00","defaultRate":"99.00","userRate95853":"160.00"}} NULL {"167750":{"billingCurrency":"USD"}} {"168010":{"userRate105709":"0.00","userRate105708":"0.00","billingCurrency":"USD","userRate86847":"0.00","userRate86976":"180.00","userRate29397":"180.00","userRate105405":"0.00","userRate29409":"0.00","userRate98814":"0.00","defaultRate":"0.00","userRate95853":"0.00"}} {"168383":{"userRate105709":"180.00","userRate105708":"180.00","billingCurrency":"USD","userRate86847":"180.00","userRate105405":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate98814":"180.00","userRate29409":"180.00","defaultRate":"180.00","userRate95853":"180.00"}} {"168475":{"userRate105709":"180.00","userRate105708":"180.00","billingCurrency":"USD","userRate86847":"180.00","userRate105405":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate98814":"180.00","userRate29409":"180.00","defaultRate":"180.00","userRate95853":"180.00"}} {"169088":{"userRate105709":"180.00","userRate105708":"90.00","userRate86847":"180.00","billingCurrency":"USD","userRate86976":"180.00","userRate29397":"250.00","userRate105405":"180.00","userRate29409":"180.00","userRate98814":"180.00","userRate106322":"180.00","defaultRate":"180.00","userRate95853":"180.00"}} {"169334":{"userRate105709":"0.00","userRate105708":"0.00","userRate86847":"0.00","billingCurrency":"USD","userRate86976":"180.00","userRate29397":"250.00","userRate105405":"0.00","userRate29409":"0.00","userRate98814":"0.00","userRate106322":"0.00","defaultRate":"0.00","userRate95853":"0.00"}} {"169619":{"userRate105709":"125.00","userRate105708":"90.00","userRate86976":"180.00","userRate29397":"250.00","userRate105405":"180.00","userRate106718":"180.00","userRate106322":"180.00","userRate106716":"180.00","userRate106717":"180.00","userRate95853":"180.00","userRate106745":"180.00","billingCurrency":"USD","userRate86847":"180.00","userRate29409":"180.00","userRate98814":"180.00","defaultRate":"180.00","lastFileCat":"479047"}} {"170242":{"userRate105709":"180.00","userRate105708":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate106718":"180.00","userRate106322":"180.00","userRate106716":"180.00","userRate95853":"180.00","userRate106717":"180.00","billingCurrency":"USD","userRate86847":"180.00","userRate98814":"180.00","userRate29409":"180.00","defaultRate":"180.00"}} NULL NULL {"170653":{"userRate105709":"125.00","userRate105708":"90.00","userRate29397":"250.00","userRate86976":"180.00","userRate106322":"0.00","userRate95853":"180.00","userRate126595":"0.00","billingCurrency":"USD","userRate86847":"250.00","userRate98814":"0.00","userRate29409":"0.00","defaultRate":"0.00","lastFileCat":"537086"}} NULL {"171318":{"lastFileCat":"485199"}} NULL {"171370":{"userRate105709":"","userRate105708":"","userRate86847":"","userRate86976":"","userRate29397":"","userRate98814":"","userRate29409":"","userRate106322":"","defaultRate":"","userRate95853":"","lastFileCat":"485199"}} {"171620":{"userRate86847":"","userRate106698":"","userRate29397":"","userRate29409":"","defaultRate":"","lastFileCat":"487225"}} {"171714":{"userRate105709":"180.00","userRate105708":"180.00","billingCurrency":"USD","userRate86847":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate98814":"180.00","userRate29409":"180.00","userRate106322":"180.00","defaultRate":"180.00","userRate95853":"180.00"}} NULL {"173055":{"userRate105709":"125.00","userRate105708":"90.00","userRate86847":"180.00","billingCurrency":"USD","userRate86976":"0.00","userRate29397":"250.00","userRate29409":"0.00","userRate98814":"0.00","userRate106322":"0.00","defaultRate":"0.00","userRate95853":"0.00","lastFileCat":"550495"}} {"173742":{"userRate105709":"180.00","userRate105708":"180.00","userRate29397":"250.00","userRate86976":"180.00","userRate106322":"180.00","userRate95853":"180.00","userRate126595":"180.00","userRate86847":"180.00","billingCurrency":"USD","userRate98814":"180.00","userRate29409":"180.00","defaultRate":"180.00","lastFileCat":"558972"}} {"174306":{"userRate126595":"180.00","userRate105709":"180.00","userRate105708":"180.00","billingCurrency":"USD","userRate86847":"180.00","userRate29397":"180.00","userRate86976":"180.00","userRate98814":"180.00","userRate29409":"180.00","userRate106322":"180.00","defaultRate":"180.00","userRate95853":"180.00"}} {"176423":{"billingCurrency":"USD"}} NULL {"181126":{"userRate126595":"0.00","userRate105709":"0.00","userRate105708":"0.00","billingCurrency":"USD","userRate86847":"0.00","userRate29397":"250.00","userRate86976":"0.00","userRate98814":"0.00","userRate29409":"0.00","userRate106322":"0.00","defaultRate":"0.00","userRate95853":"180.00"}} {"181137":{"userRate121711":"0.00","userRate105709":"125.00","userRate105708":"80.00","userRate116976":"0.00","userRate29397":"250.00","userRate86976":"160.00","userRate106322":"0.00","userRate119450":"0.00","userRate95853":"160.00","userRate126595":"160.00","userRate86847":"160.00","billingCurrency":"USD","userRate29409":"0.00","userRate98814":"0.00","defaultRate":"0.00","lastFileCat":"540143"}} NULL NULL {"185927":{"userRate105708":"90.00","userRate86847":"180.00","billingCurrency":"USD","userRate86976":"180.00","userRate29397":"250.00","userRate105405":"180.00","userRate29409":"180.00","userRate98814":"180.00","defaultRate":"180","userRate95853":"180.00"}} {"187513":{"userRate105708":"90.00","billingCurrency":"USD","userRate86847":"180.00","userRate105405":"180.00","userRate29397":"250.00","userRate86976":"180.00","userRate98814":"180.00","userRate29409":"180.00","defaultRate":"180","userRate95853":"180.00"}} {"187702":{"userRate105709":"125.00","userRate105708":"90.00","userRate29397":"250.00","userRate86976":"180.00","userRate106322":"180.00","userRate95853":"180.00","userRate126595":"180.00","billingCurrency":"USD","userRate86847":"250.00","userRate98814":"180.00","userRate29409":"180.00","defaultRate":"180.00","lastFileCat":"537086"}} NULL
What I am trying to do is parse/split this information out into either multiple columns, or multiple rows of data... Either is fine (although multiple rows may be easier to join to other things later).
Ideally, the final data would look something like this (using the first row of data from my example above):
(Please excuse my limited Bulletin Board Formatting knowledge)Column Name: Value ProjectID: 23953 UserRateID1: 29441 UserRate1: 160.00 UserRateID2: 58591 UserRate2: 160.00 UserRateID3: 57694 UserRate3: 15.00 UserRateID4: 97056 UserRate4: 90.00 UserRateID5: 29441 UserRate5: 160.00 and so on...
Note that there is also a "Billing Currency" (USD) and "DefaultRate" (180.00), which would need parsed in there. It *appears* to be dynamic as far as the number of "UserRateIDs" and where exactly the "Billing Currency" value shows up.
Ultimately, I'll be taking the ID and the Rate and using that information for calculations with data from other tables... Time Spent (I already have from another table) x Rate (the example data I'm trying to parse) = Invoice Amt. For example, UserID 29441 spends two hours on a task for Project ID 23953 at an Hourly Rate of $160.00 USD would result in an invoice for $320.00 USD.
I have spent some time googling for possible solutions, but I can't see any that deal with the multiple 'delimiters'...
Any assistance I can get on how to approach this (seemingly dynamic and complex problem) would be most appreciated!
Thanks in advance! 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Here's an option. Make sure that you understand this and ask any questions that you might have. You can find the code for the function in this article.

CREATE TABLE #SampleData( longtext varchar(8000) );

INSERT INTO #SampleData VALUES ('{"23953":{"userRate29441":"160.00","userRate58591":"160.00","userRate57694":"15.00","userRate97056":"90.00","userRate86976":"180.00","userRate29397":"250.00","userRate29396":"17.20","userRate95853":"180.00","userRate126595":"180.00","userRate48853":"15.00","userRate29439":"15.00","userRate86847":"180.00","userRate29443":"15.00","billingCurrency":"USD","userRate29437":"15.00","userRate82065":"160.00","userRate29445":"15.00","userRate29409":"15.00","userRate29444":"15.00","userRate29447":"15.00","defaultRate":"180.00"}}'), ('{"23970":{"userRate29848":"160.00","userRate29441":"160.00","userRate58591":"160.00","files-listMode":"list","userRate57694":"160.00","userRate30031":"160.00","userRate29840":"160.00","userRate29397":"160.00","userRate29396":"160.00","userRate48853":"90.00","billingCurrency":"USD","userRate29437":"160.00","project-notebooks-sortmode":"bydate","userRate29409":"160.00","userRate29433":"0.00","userRate50858":"160.00","defaultRate":"160.00","userRate29449":"160.00","userRate29500":"0"}}'), ('{"23973":{"userRate29441":"160","files-listMode":"list","userRate48853":"160","files-sortMode":"bydate","userRate29437":"160","userRate29397":"160","userRate29409":"160","project-notebooks-sortmode":"bycat","defaultRate":"160"}}'), ('{"23981":{"userRate29441":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate97482":"160.00","userRate95853":"160.00","userRate86847":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate30289":"160.00","project-notebooks-sortmode":"bydate","userRate29409":"160.00","userRate29449":"160.00","defaultRate":"160.00"}}'), ('{"24139":{"userRate29441":"160.00","userRate58591":"160.00","userRate41810":"160.00","userRate57694":"160.00","userRate29397":"160.00","userRate29396":"160","userRate41809":"160.00","userRate48853":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate29409":"160.00","project-notebooks-sortmode":"byname","defaultRate":"160.00"}}'), ('{"24303":{"files-listMode":"list"}}'), ('{"28722":{"userRate29441":"160.00","userRate58591":"0.00","files-listMode":"grid","userRate48853":"160.00","userRate57694":"0.00","billingCurrency":"USD","userRate29437":"0.00","lastNoteBkCat":"27898","userRate29397":"160.00","userRate29409":"0.00","userRate36761":"0.00","defaultRate":"0.00"}}'), ('{"32251":{"userRate29441":"160.00","userRate58591":"160.00","userRate45081":"160.00","userRate66073":"160.00","userRate57694":"160.00","lastNoteBkCat":"6546","userRate86976":"160.00","userRate29397":"160.00","userRate95853":"160.00","userRate48853":"160.00","billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29437":"160.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"31729"}}'), ('{"38469":{"userRate29441":"160.00","userRate58591":"0.00","files-listMode":"list","userRate48853":"160.00","userRate57694":"0.00","billingCurrency":"USD","lastNoteBkCat":"9037","userRate29437":"0.00","userRate29397":"160.00","userRate29409":"0.00","defaultRate":"0.00"}}'), ('{"43808":{"userRate29441":"160","userRate48853":"160","userRate57694":"160","billingCurrency":"USD","userRate29437":"160","userRate29397":"160","userRate29409":"160","defaultRate":"160"}}'), ('{"49434":{"userRate29441":"45.00","userRate48853":"45.00","userRate57695":"45.00","userRate57694":"45.00","userRate29437":"45.00","userRate29397":"45.00","userRate29409":"45.00","defaultRate":"45.00"}}'), ('{"58855":{"userRate29441":"160.00","userRate58591":"160.00","userRate48853":"160.00","userRate57694":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"59151":{"userRate29441":"160.00","userRate58591":"160.00","userRate57694":"120.00","lastNoteBkCat":"24215","userRate86976":"160.00","userRate29397":"240.00","userRate75047":"160.00","userRate95853":"160.00","userRate48853":"240.00","billingCurrency":"USD","userRate82065":"160.00","userRate29437":"0.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"53222"}}'), ('{"60802":{"billingCurrency":"USD","userRate82065":"180.00","userRate86976":"180.00","userRate29397":"250.00","userRate29409":"0.00","defaultRate":"160.00","userRate95853":"180.00"}}'), (NULL), ('{"68135":{"userRate29441":"160.00","userRate58591":"0.00","userRate57694":"0.00","lastNoteBkCat":"16117","userRate29397":"0.00","userRate71587":"0.00","userRate48853":"160.00","files-sortMode":"byname","billingCurrency":"USD","userRate29437":"0.00","userRate29409":"0.00","defaultRate":"0.00","lastFileCat":"46423"}}'), ('{"72640":{"userRate29441":"0.00","userRate58591":"160.00","userRate48853":"0.00","userRate57694":"0.00","billingCurrency":"USD","userRate82065":"160.00","userRate29437":"0.00","userRate29397":"160.00","userRate29409":"0.00","defaultRate":"160.00"}}'), ('{"74618":{"userRate29441":"100.00","userRate58591":"0.00","userRate48853":"0.00","userRate57694":"0.00","billingCurrency":"USD","userRate29437":"0.00","userRate29397":"0.00","userRate29409":"0.00","defaultRate":"0.00"}}'), ('{"78635":{"userRate29441":"0.00","userRate58591":"0.00","userRate48853":"0.00","userRate57694":"0.00","billingCurrency":"USD","userRate82065":"160.00","userRate29437":"0.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"88280":{"userRate29441":"160.00","userRate58591":"160.00","userRate48853":"160.00","userRate57694":"160.00","billingCurrency":"USD","userRate29437":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"88414":{"userRate29441":"99.00","userRate58591":"0.00","userRate57694":"0.00","userRate105405":"99.00","userRate97056":"99.00","userRate86976":"99.00","userRate29397":"99.00","userRate97482":"99.00","userRate95853":"99.00","userRate48853":"0.00","userRate86847":"99.00","billingCurrency":"USD","userRate82065":"99.00","userRate29437":"0.00","userRate29409":"99.00","defaultRate":"99.00"}}'), ('{"102167":{"billingCurrency":"USD"}}'), ('{"104857":{"billingCurrency":"USD"}}'), ('{"107601":{"userRate58591":"160.00","userRate57694":"160.00","userRate84842":"160.00","lastNoteBkCat":"37414","userRate29397":"160.00","userRate84847":"160.00","userRate84845":"160.00","userRate84846":"160.00","userRate84844":"160.00","billingCurrency":"USD","files-sortMode":"bydate","userRate82065":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"108471":{"userRate58591":"160.00","userRate57694":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"110102":{"userRate86847":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"114972":{"userRate86847":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"121219":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}}'), ('{"123134":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"124749":{"userRate86847":"160.00","billingCurrency":"USD","userRate82065":"160.00","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"126543":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00"}}'), ('{"130338":{"billingCurrency":"USD","userRate86847":"160.00","userRate82065":"160.00","userRate29397":"160.00","userRate86976":"160.00","userRate29409":"160.00","defaultRate":"160.00","userRate95853":"160.00"}}'), ('{"132207":{"lastMsgCat":"0","userRate97056":"0.00","userRate86976":"160.00","userRate29397":"250.00","userRate94885":"0.00","userRate94884":"0.00","userRate97482":"160.00","userRate95853":"160.00","userRate95547":"0.00","billingCurrency":"USD","userRate86847":"160.00","userRate96581":"0.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"299329"}}'), ('{"132219":{"billingCurrency":"USD","userRate86847":"160.00","lastNoteBkCat":"59340","userRate86976":"160.00","userRate29397":"160.00","userRate29409":"160.00","defaultRate":"160.00","lastFileCat":"347605"}}');

SELECT d.longtext, x.id, LEFT( s.Item, CHARINDEX('":"', s.Item +'":"') - 1), SUBSTRING( s.Item, CHARINDEX('":"', s.Item)+3, 8000) FROM #SampleData d CROSS APPLY( SELECT SUBSTRING( longtext, 3, CHARINDEX('":{"', longtext)-3), REPLACE( REPLACE( SUBSTRING( longtext, CHARINDEX('":{"', longtext)+4, 8000), '","', CHAR(7)), '"}}', ''))x(id, valueset) CROSS APPLY dbo.DelimitedSplit8K(valueset, CHAR(7))s

GO DROP TABLE #SampleData

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RMason - eLIMBS, LLC Mr or Mrs. 500 Points: 504 More actions · Answer 2

Thanks for the quick response, Luis! So far, this seems to work very well for my needs.

Thanks again! 😀:D:D:D:D:D:D

edit: I will say that I'm a bit confused about the CROSS APPLY, and how it works, so I'll be reading up on that.