CODEWITHSUNDEEP
sqlt-sqlsql-server-2014
citizenkraft
citizenkraft

Reputation: 23

SQL Splitting a twice delimited string into a two column table

The problem that I'm running into is splitting a twice delimited string into a two column table. I've found plenty of resources on converting a string into a table using a single delimiter but have had a tough time with two.

The use case for this is for a ecommerce filtering stored proc. The client can manage the filters themselves (the Id) so the stored proc needs to work with a dynamic amount of conditions. A user selects 2 attributes under the first filter, then 3 attributes under a second filter and so on. Performance is paramount, but we will be processing a small amount of data, and I've found xml parsing to be the quickest, but my skills in that realm are modest at best.

I've been looking at this article but have been having trouble wrapping my head around it: https://www.mssqltips.com/sqlservertip/1771/splitting-delimited-strings-using-xml-in-sql-server/

What i'd like to achieve is a function that returns the following table...

Input: 14-11,12,13|15-21,22,23
Output:
Id | Values
14 | 11, 12, 13
15 | 21, 22, 23

I will post updates as I continue down this path.

UPDATE:

Actually, I was thinking about this and I think the better solution to this problem might be an output of... 

Id | Values 
14 | 11 
14 | 12 
14 | 13 
15 | 21 
15 | 22
15 | 23

That would allow for set based transactions on the results which should speed things up.

Upvotes: 1

Views: 2266

Answers (1)

FutbolFan
FutbolFan

Reputation: 13763

Using left, charindex and substring function of MSSQL, you could do this easily:

create table test(col1 varchar(50));

insert into test values
('14-11,12,13'),
('15-21,22,23');

select left(col1,charindex('-',col1)-1) as id, 
       substring(col1,charindex('-',col1)+1,len(col1)-charindex('-',col1)) as [values]
   from test;

SQL Fiddle Demo

For your updated version, you could use XML to parse your string and convert it into rows like this:

SELECT A.id
    ,Split.a.value('.', 'VARCHAR(100)') AS [Values]
FROM (
    SELECT left(col1, charindex('-', col1) - 1) AS id
        ,CAST('<M>' + REPLACE(substring(col1, charindex('-', col1) + 1
            , len(col1) - charindex('-', col1)), ',', '</M><M>') + '</M>' AS XML) AS String
    FROM test
    ) AS A
CROSS APPLY String.nodes('/M') AS Split(a);

Credit: @SRIRAM's answer

SQL Fiddle Demo2

Upvotes: 1

Related Questions