Wednesday, May 22, 2013

Get the Nth positioned string from a delimited list

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.




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