CODEWITHSUNDEEP
sqlsql-serverstringsplit
user11589950
user11589950

Reputation: 3

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.

Upvotes: 0

Views: 121

Answers (2)

SQLpro
SQLpro

Reputation: 5113

Something like :

SELECT ID, S.value
FROM   Data
       CROSS APPLY STRING_SPLIT(REPLACE(',' + String, ',1,', '#1,'), '#') AS S
WHERE  value LIKE '1,%'

?

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 2

Related Questions