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
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
Thanks , I am gonna use c# now.. seems like thats the better way. Thanks for ur comments .. they were very useful
-- 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);
19 People are following this question.
