CODEWITHSUNDEEP
sqlsql-servert-sql
Vaibhav Sharma
Vaibhav Sharma

Reputation: 98

Fetch specific string based on pattern

I have a column with data like {"Type":1,"ApplicationID":1,} or 

{"Type":1,"ApplicationID":158,} 
or 
{"Type":1,"ApplicationID":234,"TarifType":1,"TarifID":2} or  etc.

I tried with 

SELECT PATINDEX('%"ApplicationID":___,%', '{"Type":1,"ApplicationID":177,}') 
or
SELECT PATINDEX('%"ApplicationID":[0-9][0-9][0-9],%', '{"Type":1,"ApplicationID":177,}')

to fetch the index of expression it returns a value.

But the problem is the application id can be any number from 1 to int (max) so we cannot fix the number of integer chars as it is in above queries.

If i can find the correct path index than, I might be able to find the substring later.

Is there any way to do this? I need to use the application ID in subquery or cor-related query. Otherwise we can store it in some temp table.

Please suggest a way to fetch this id.

Upvotes: 2

Views: 84

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81940

If NOT 2016

Just for fun, and perhaps overkill, you can convert your JSON string into XML, and then extact desired values/attributes.

I added an additional replace(JSON_String,',}','}') to trap the malformed JSON string. Can be removed if sample data was a typo.

Example

Declare @YourTable table (ID int,JSON_String varchar(max))
Insert Into @YourTable values
 (1,'{"Type":1,"ApplicationID":1,}')
,(2,'{"Type":1,"ApplicationID":158,}')
,(3,'{"Type":1,"ApplicationID":234,"TarifType":1,"TarifID":2}')

Select A.ID
      ,[Type]          = XMLData.value('(row/@Type)[1]'         , 'int') 
      ,[ApplicationID] = XMLData.value('(row/@ApplicationID)[1]', 'int') 
      ,[TarifID]       = XMLData.value('(row/@TarifID)[1]'      , 'int') 
 From  @YourTable  A
 Cross Apply (values (convert(xml,replace(replace(replace(replace(replace(replace(JSON_String,',}','}'),'"',''),'{','<row '),'}','"/>'),':','="'),',','" ')))
             ) B (XMLData)

Returns

enter image description here

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269673

Why can't you just look for the keyword?

SELECT PATINDEX('%"ApplicationID":%', '{"Type":1,"ApplicationID":177,}')

I don't see a need to match the value, just the keyword, especially given that the keyword is already in double quotes.

Also, I should point out that to get a match to values that have that keyword, use LIKE:

WHERE col LIKE '%"ApplicationID":%'

Upvotes: 2

Related Questions