Fetch specific string based on pattern

Question

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.

Thanks John for formating.

Vaibhav Sharma
– Vaibhav Sharma

2017-05-27 14:24:12 +00:00
Commented May 27, 2017 at 14:24 — Vaibhav Sharma
– Vaibhav Sharma, Commented May 27, 2017 at 14:24
what is data type of that column ?

Fahad Anjum
– Fahad Anjum

2017-05-27 14:41:54 +00:00
Commented May 27, 2017 at 14:41 — Fahad Anjum
– Fahad Anjum, Commented May 27, 2017 at 14:41
Data type is nvarchar

Vaibhav Sharma
– Vaibhav Sharma

2017-05-27 14:47:26 +00:00
Commented May 27, 2017 at 14:47 — Vaibhav Sharma
– Vaibhav Sharma, Commented May 27, 2017 at 14:47

John Cappelletti · Accepted Answer · 2017-05-27 16:18:56Z

3

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

edited May 27, 2017 at 16:18

answered May 27, 2017 at 15:41

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vaibhav Sharma Over a year ago

thanks for your help. We are using SQL 2014 and it worked. SQL 2016 JSON functions are not available.

John Cappelletti Over a year ago

@VaibhavSharma Happy it helped

John Cappelletti Over a year ago

@VaibhavSharma Just a side note, since you are 2014, I would recommend try_convert(xml, --- ) rather than convert(xml, --- ). This will return a null just in case your (presumably) simple JSON string is not formatted correctly.

Gordon Linoff · Accepted Answer · 2017-05-27 14:31:47Z

2

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":%'

answered May 27, 2017 at 14:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Vaibhav Sharma Over a year ago

yes, Gordorn it can be done. Actually, I was trying to fetch the value part so I was a bit experimenting which went wrongly. I can find the initial index. But stuck with the finding the value. Tried SELECT SUBSTRING('{"Type":1,"ApplicationID":177,}', PATINDEX('%"ApplicationID":%', '{"Type":1,"ApplicationID":177,}') +16, LEN('{"Type":1,"ApplicationID":177,}')) to get data but it gives the end comma. Actually it shall give a more lengthy string based on the length of data being worked upon

Gordon Linoff Over a year ago

@VaibhavSharma . . . If you are using SQL Server 2016, you should investigate the JSON functions (learn.microsoft.com/en-us/sql/t-sql/functions/…). If you are using SQL Server and JSON and not using 2016, then consider upgrading.

Collectives™ on Stack Overflow

Fetch specific string based on pattern

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related