1

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.

FutbolFan
  • 13,235
  • 3
  • 23
  • 35
citizenkraft
  • 23
  • 7

1 Answers1

1

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

Community
  • 1
  • 1
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
  • Yep, that'll work for the original question, thanks! Do you think i would achieve better performance by doing it using a table as described in the update above? – citizenkraft Aug 14 '15 at 18:12
  • It depends really. Your updated version would be easier and probably more efficient to retrieve if you are looking to find only certain `values` for each `id`. You could follow the XML method (not recommended), but you can split your values into separate rows. http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows – FutbolFan Aug 14 '15 at 18:16
  • Yes, it is filtering out products that match any of the criteria of the first filter, and then any of the products that match the criteria of the second. So the query could be something like `SELECT productid FROM productAttribute where attributeId in (select value from #splitTable where Id = @AttributeIdImIteratingOver)` – citizenkraft Aug 14 '15 at 18:21
  • Thats perfect FutbolFan! Thanks! I'd give you all the upvotes, but I dont have the rep to do so. You will forever have my unyielding gratitude. – citizenkraft Aug 14 '15 at 18:24
  • Then yes definitely, insert the second query I gave into your temp table `#splitTable`. Then. that should work for you as intended. – FutbolFan Aug 14 '15 at 18:25