Split SQL string with specific string instead of separator?

I have table that looks like:

|ID  | String
|546 | 1,2,1,5,7,8
|486 | 2,4,8,1,5,1
|465 | 18,11,20,1,4,18,11
|484 | 11,10,11,12,50,11

I want to split the string to this:

|ID  | String
|546 | 1,2
|546 | 1,5
|486 | 1,5,1
|486 | 1
|465 | 1,4

My goal is to show ID and all the strings starting with 1 with just the next number after them.

I filtered all rows without '%1,%' and I don’t know how to continue.

Answer

If you use SQL Server 2016+, you may try to use a JSON-based approach. You need to transform the data into a valid JSON array and parse the JSON array with OPENJSON(). Note that STRING_SPLIT() is not an option here, because as is mentioned in the documentation, the output rows might be in any order and the order is not guaranteed to match the order of the substrings in the input string.

Table:

CREATE TABLE Data (
   ID int,
   [String] varchar(100)
)
INSERT INTO Data
   (ID, [String])
VALUES   
   (546, '1,2,1,5,7,8'),
   (486, '2,4,8,1,5,1'),
   (465, '18,11,20,1,4,18,11'),
   (484, '11,10,11,12,50,11')

Statement:

SELECT
   ID,
   CONCAT(FirstValue, ',', SecondValue) AS [String]
FROM (   
   SELECT 
      d.ID, 
      j.[value] As FirstValue, 
      LEAD(j.[value]) OVER (PARTITION BY d.ID ORDER BY CONVERT(int, j.[key])) AS SecondValue
   FROM Data d
   CROSS APPLY OPENJSON(CONCAT('[', d.[String], ']')) j
) t
WHERE t.FirstValue = '1'

Result:

----------
ID  String
----------
465 1,4
486 1,5
486 1,
546 1,2
546 1,5