Splitting a string with a recursive CTE is usually a bad choice but this is a case where I at least would consider a solution using that.
If you used a faster string split technique you would have to preserve the order of items and rebuild parent_category
using for xml
. That might still be faster than using a recursive CTE but here is the recursive stuff anyway.
SQL Fiddle
MS SQL Server 2008 Schema Setup:
create table YourTable
(
category_path nvarchar(max)
);
insert into YourTable values
('RootCategory\Middle Category\Child Category\Child of child category'),
('RootCategory\Middle Category\Other Child Category\');
Query 1:
with C as
(
select left(category_path, charindex('\', category_path+'\') - 1) as category_name,
stuff(category_path, 1, charindex('\', category_path+'\'), '') as category_path,
cast(null as nvarchar(max)) as parent_path
from YourTable
union all
select left(category_path, charindex('\', category_path+'\') - 1),
stuff(category_path, 1, charindex('\', category_path+'\'), ''),
coalesce(parent_path+'\', '')+category_name
from C
where category_path <> ''
)
select distinct
category_name,
parent_path
from C;
Results:
| CATEGORY_NAME | PARENT_PATH |
-------------------------------------------------------------------------
| Child Category | RootCategory\Middle Category |
| Child of child category | RootCategory\Middle Category\Child Category |
| Middle Category | RootCategory |
| Other Child Category | RootCategory\Middle Category |
| RootCategory | (null) |
I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work:
SELECT *
FROM table AS T
JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L
ON T.id = L.row_value;
If the id_list is actually stored in another table (say list_table) you need to also use a CROSS APPLY
operator (http://dcx.sybase.com/1101/en/dbusage_en11/apply-joins-joinsasp.html):
SELECT *
FROM list_table AS LT
CROSS APPLY sa_split_list(LT.id_list) L
JOIN table T
ON T.id = L.row_value
AND <optional: some other condition that ties rows in list_table to rows in table, like a group membership or location>
This all requires that only numbers are part of these list as the split pieces will be automatically cast to the datatype of the id column. If that is not the case you can use this simpler, but slower technique:
SELECT *
FROM table
WHERE ','+@id_list+',' LIKE '%,'+CAST(id AS VARCHAR(20))+',%'
That last example assumed that the id list is in a variable @id_list. if it is in another table you can use the same CROSS APPLY
technique from above. The additional commata make sure that the first or last entry can be matched.
Best Answer
It is not neccesary to modify Jeff Moden's function. I have devised a little bit sophisticated way of achieving the result just using the original function.
First Here it is the original Moden's function: (Source: http://www.sqlservercentral.com/articles/Tally+Table/72993/)
And the query for obtaining the required result (I have created a CTE just for clarity)
EDITED: I realized that my solution assummed that id value was ordered or that the order in the original string was not neccesary to be preserved in the result. Below the general solution (note that I changed the first number in the string for testing the situation)
http://sqlfiddle.com/#!6/31ccd/3