0

I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId Value from that substring entry pulling a result set that contains a row for each LoanId. Any help would be greatly appreciated.

A considerably abbreviated JSON string containing the stuff I'm looking for.

[
  {"loanId":"1111111111","someotherValue":7},
  {"loanId":"2222222222","someotherValue":4},
  {"loanId":"3333333333","someotherValue":5},
] 
  • I'm actually trying one new solution now based on red-gate.com/simple-talk/sql/t-sql-programming/… – Tim Feb 22 at 20:59
  • 1
    not as an answer of course, but if possible - migrate to 2016 where JSON supported, – a_vlad Feb 23 at 9:48
  • Can you show what output you want exactly? Are you expecting a row per loanId, or a single row with three comma-separated values, or three distinct columns, or ...? – Aaron Bertrand Feb 23 at 18:29
  • Output would be a row for every loanid. I updated the op. We’re not able to update to 2016 at this time. It’s on the road map for this year though. – Tim Feb 23 at 20:04
0

First, create a string splitting function. For simplicity I'm going to use an XML splitter, but you can see lots of discussion about it here and in all the links that points you to.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
   RETURN ( SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM ( SELECT x = CONVERT(xml, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')) AS a 
          CROSS APPLY x.nodes('i') AS y(i));
GO

With the function in place, you can now use it to chop apart this JSON string:

DECLARE @s varchar(max) = '[
  {"loanId":"1111111111","someotherValue":7},
  {"loanId":"2222222222","someotherValue":4},
  {"loanId":"3333333333","someotherValue":5},
]';

SELECT LoanID = PARSENAME(s2.Item,1) 
FROM dbo.SplitStrings_XML(@s, ',') AS s
CROSS APPLY dbo.SplitStrings_XML(s.Item, ':') AS s2
WHERE s.Item LIKE '%"loanId":"%'
AND s2.Item NOT LIKE '%"loanId"%';

Results:

LoanID
----------
1111111111
2222222222
3333333333

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.