Select All Instances of Substring in a String

Question

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},
]

score 1 · Answer 1 · answered Feb 26 '19 at 13:29

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

Select All Instances of Substring in a String

1 Answers1