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

• Tim: I'm actually trying one new solution now based on red-gate.com/simple-talk/sql/t-sql-programming/…
• a_vlad: not as an answer of course, but if possible - migrate to 2016 where JSON supported,
• Aaron Bertrand: 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 ...?
• Tim: 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.

Answer 1

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