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 found this solution :
mysql> update mytab set difference=(template not like concat('%|',value,'|%'));
Query OK, 0 rows affected (0.05 sec)
Rows matched: 3 Changed: 0 Warnings: 0
mysql> select * from mytab;
+----+-------+----------------+------------+
| id | value | template | difference |
+----+-------+----------------+------------+
| 1 | 7 | |2|5|7|8|9|12| | 0 |
| 2 | 0 | |5| | 1 |
| 3 | 5 | |2|5|7|8|9|12| | 0 |
+----+-------+----------------+------------+
3 rows in set (0.00
sec)
Best Answer
It's usually up to you to do that via normalisation. Have a table with unique URLs in it, and link to that by its ID.