Data :
Tree Depth
URL1||URL2 2
URL2||URL3 2
URL3||URL4||URL5 3
URL1||URL2||URL3 3
In the above data the Tree column consists of string separated by "||". I need to convert the above data such that I have 3 columns (since the max depth is 3 in this example) , the result should look like:
COL1 COL2 COL3 DEPTH
URL1 URL2 2
URL2 URL3 2
URL3 URL4 URL5 3
URL1 URL2 URL3 3
In the above example the max depth is 3 however in real world it could be N number.
Good day,
In first glance it is look like we need to use user defined SPLIT function but since number of values that you have in each string is not more then 4, there is a much simpler and probably much better solution. We just need to use the built-in PARSENAME function.
I did not test the code but the solution should be something like this:
I replace the || with dot, since PARSENAME parse names that split by dot. this is the trick :-)
I actually mentioned example like this in my lecture at the sqlsaturday #360. You can see the presentation. The lecture was about WHY to use SQLCLR, and not less important WHEN to use it over transact-SQL. but I also talked about when NOT to use it, and this was one of the examples there.
In any case! if you are going to use SPLIT function then you should use SQLCLR and not T-SQL, as you can see here.