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...
rmeldrum's avatar
rmeldrum
Copper Contributor
Feb 13, 2024

buzza1234 

 

You can use:

 

 

SELECT JSON_VALUE(tableName.columnName, '$.cid') AS cid,
       JSON_VALUE(tableName.columnName, '$.ID') AS id,
       JSON_VALUE(tableName.columnName, '$.Email') AS email
FROM   dbo.tableName

 

 

  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Feb 13, 2024

    rmeldrum 

     

    That's already been discussed.

     

    The SQL Server JSON functions only work with standards-compliant JSON data, and the data Asita is working with is not always standards-compliant.

     

    Until the data is completely standards-compliant, the SQL JSON functionality cannot be used.

     

    Cheers,

    Lain

Resources