I have an SQL Server database where there is a cell with a delimiter (
\) separated string. An example of this string would be:
category_path ============= RootCategory\Middle Category\Child Category\Child of child category RootCategory\Middle Category\Other Child Category\
There are a lot of category paths like this. I would like to parse all of them into an other table:
category_name parent_path ==================================== RootCategory null Middle Category RootCategory Child Category RootCategory\Middle Category Child of child category RootCategory\Middle Category\Child Category Other Child Category RootCategory\Middle Category
I have left out the duplicates here, but the result could contain duplicates (I will need to cursor over the rows of this table later, and I can call distinct there).
There are a lot of examples around the net where a string is simply splitted. (Here for example) I was thinking about writing a query where I split the string, cursor over the results and accumlate the parent_path in each step, but that seems to be very suboptimal.
Is there a solution where I wouldn't have to declare so many cursors for a simple string?