question

aRookieBIdev avatar image
aRookieBIdev asked

Complex string - Use sql to parse and find value.

This is gonna be very difficult I guess. I have been breaking my head on this. I am reading data from spring framework tables(a Java framework for executing batch). The data in a field is as mentioned below, '{"map":{"entry":[{"string":["processingFilePath","\/opt\/edge\/ingest\/inbox\/62662.P.D11102015T125249.P.xml"]},{"string":["fileHash","ce0f5b55d4f0c8d5c3f8af99878a506f210870a2"]},{"string":"jobId","long":7},{"string":["fileName","62662.P.D11102015T125249.P.xml"]},{"string":["submissionType","P"]}]}}' I need to pick up the JobId value which is no "7" from above, and the filename "62662.P.D11102015T125249.P.xml" . Please send in your suggestions to parse this using sql. Thanks, kannan
sql-server-2012string-functionparse
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Not sure if this will get you going in the right direction. It's a bit old, but seems promising (SQL CLR) - http://www.sqlservercentral.com/articles/SQLCLR/74160/ The project is hosted on GitHub nowadays - https://github.com/JamesNK/Newtonsoft.Json Sql 2016? - https://msdn.microsoft.com/en-us/library/dn921897.aspx
3 Likes 3 ·
Thanks , I am gonna use c# now.. seems like thats the better way. Thanks for ur comments .. they were very useful
0 Likes 0 ·

1 Answer

· Write an Answer
danjumar avatar image
danjumar answered
-- This seems to work DECLARE @theString varchar(500); DECLARE @StartPosition int; DECLARE @Sub1 varchar(500); SET @theString = '{"map":{"entry":[{"string":["processingFilePath","\/opt\/edge\/ingest\/inbox\/62662.P.D11102015T125249.P.xml"]},{"string":["fileHash","ce0f5b55d4f0c8d5c3f8af99878a506f210870a2"]},{"string":"jobId","long":7},{"string":["fileName","62662.P.D11102015T125249.P.xml"]},{"string":["submissionType","P"]}]}}'; SET @StartPosition = PATINDEX('%"jobId","long":%}', @theString) + 15; SELECT @Sub1 = SUBSTRING(@theString, @StartPosition, LEN(@theString)); SELECT SUBSTRING(@Sub1, 1, CHARINDEX('}', @Sub1) - 1);
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.