Forum Discussion

buzza1234's avatar
buzza1234
Copper Contributor
Jan 24, 2024

How To Extract Values from the string that is key value pairs with different formats

I have a table called DataForce. with column DataVal (Nvarchar 2000). it has below 3 records How to get CID Value, ID Value, Email Value, Zip from below strings please advise?   {'ani':'80052242...
LainRobertson's avatar
LainRobertson
Silver Contributor
Jan 25, 2024

buzza1234 

 

Hi, Asita.

 

I can't think of any reliable way of handling the conversions using out-of-the-box SQL string functions, no. Given even a small presentation difference would need a different handler, the problem just scales out into too many combinations too quickly.

 

If I were faced with this poorly-presented data, I'd be pursuing the following, in order of most preferred to least preferred:

 

  • Get the data presented uniformly, and ideally using the standards-compliant JSON format;
  • Write a CLR module to handle the parsing (not an option if you're using Azure SQL Database; you need Azure SQL MI or SQL Server for this option);
  • Introduce a two-step process where:
    • Step one would be to author a stored procedure to standardise the data presentation to JSON format;
    • Step two would be to use the JSON functions within any queries/views (so, the same approach as the top point in this list).

 

Cheers,

Lain

buzza1234's avatar
buzza1234
Copper Contributor
Jan 29, 2024

LainRobertson Thank you for your guide lines and advise

 

here is what I tried and able to get in

 

DROP TABLE IF EXISTS #Mytemptable;

CREATE TABLE #Mytemptable
(
    Id INT IDENTITY(1, 1)
   ,Dataval NVARCHAR(MAX) NOT NULL
);

INSERT #Mytemptable
(
    Dataval
)
VALUES
(N'{''ani'':''80052242'',''dnis'':''90425935'',''cid'':''4601923812'',''Id'':''046067ce-7fec-4af7-bb51-e4d31b4720'',''No'':''3048'',''FN'':''Bod Conner'',''Ent'':''2012-08-27'',''Email'':''email address removed for privacy reasons'',''Add'':''P.o. Box 146'',''Zip'':''31040'',''Not'':''English'',''Off'':''FL'',''ACode'':'''',''OffId'':''0002 Home Office FL'',''Except'':''''}, ' -- Dataval - nvarchar(max)
    )
,(N'{"ani":"30926539","dnis":"33300180","cid":"4594296356","Id":"a2a5bac2-b6ad-4239-95b2-2da730c2b6","No":"93872","FN":"Martinael u0026 Kar Schmale","Ent":"2023-07-08","Email":"email address removed for privacy reasons","Add":"552 WPlainview cir","Zip":"13498","Not":"","Off":"FL","ACode":"1|a2a5bac2-b6ad-4239-95b2-2da780c2b6|0061FL|V|000000000000000000|00000000","OffId":"0061 Rand FL","Except":""},')
,(N'{ani:63179364,dnis:88825454,cid:4563900520,Id:c1144de0-1945-4862-b057-fe25eabe73,No:97158,FN:Bombay and Marsha Osrow,Ent:2023-11-03,Email:email address removed for privacy reasons,Add:10014 SW Vitalia Court,Zip:14987,Not:English,Off:FL,ACode:,OffId:0021 Zanfini FL,Except:},');


SELECT
    jn.Id
,   cid = MAX (REPLACE (IIF(c2.ordinal = 3, c2.value, ''), 'cid:', ''))
,   Id = MAX (REPLACE (IIF(c2.ordinal = 4, c2.value, ''), 'Id:', ''))
,   Email = MAX (REPLACE (IIF(c2.ordinal = 8, c2.value, ''), 'Email:', ''))
FROM #Mytemptable jn
    CROSS APPLY
(SELECT Removedquotes = REPLACE (REPLACE (jn.Dataval, '''', ''), '"', '')) c1
    CROSS APPLY
(SELECT ss.* FROM STRING_SPLIT(c1.Removedquotes, ',', 1) ss) c2
GROUP BY jn.Id
ORDER BY jn.Id;

Resources