Today had a colleague asking me on a method to return particular positioned string from a delimited list. The scenario was like below
Consider a string like abcd-efgh-jkl-mnop-qrst-uvwx. The requirement was to get 3rd string from it ie ijkl. The usual way of doing this was to use a udf to parse and split the string and then use logic to get Nth string. But my colleague wanted to check if there are any methods which he could use inline and dispense with the usage of UDF. I came up with the below solution which I’m sharing here for others benefit.
Consider a string like abcd-efgh-jkl-mnop-qrst-uvwx. The requirement was to get 3rd string from it ie ijkl. The usual way of doing this was to use a udf to parse and split the string and then use logic to get Nth string. But my colleague wanted to check if there are any methods which he could use inline and dispense with the usage of UDF. I came up with the below solution which I’m sharing here for others benefit.
The solution is based on the below approach
The attempt is to built an XML out of the given delimited string. Using REPLACE() function the dilimiter is replaced by a dummy Node elements. Once built as a XML document, we can use query() method to search and return Nth string by using condition check based on position() function. Then value() function is applied over it to return the node value as a string.
The solution can be extended to pass position value from a variable or parameter to return any occurance of the string. The modified solution would be as below
Pass any value for @pos and you will get the corresponding positioned string returned
Using XML for this can lead to very poor performance (lots of overhead and memory use, even for a simple small string). XML parsing like that can also blow up if the string contains "xml-special" characters.
Here is a MUCH better way to split strings: http://www.sqlservercentral.com/articles/Tally+Table/72993/
And if you have few elements or want one of the early ones in a many-delimited string, this is great too:
http://www.sqlservercentral.com/articles/T-SQL/97545/
Kevin G. Boles
SQL Server Consultant
SQL Server MVP 2007-2012
Indicium Resources, Inc.
Yep agree that XML special string can blow it up. But for that you can prepare string beforehand to avoid the characters if possible.
I couldnt find a drastic change in performance in my case.
Definitely Tally table is better method but in my case the OP wanted a method to be use inline within same statement.