Is there a function in SQL Server 2008 or higher to return the Nth element of a delimited string? For example, let's say that I have a string like 'apple|orange|banana' and I want to get the 2nd element in this string which is 'orange'. Is there any built-in function to do this - something like GetElement('apple|orange|banana',2,'|') where you could pass the delimited string, the element number, and the delimiter? If not, then could someone please show me the code to do this programmatically?
You can use a string-splitter function to "split" the string at a given Delimiter (the | character in this case), and then you can filter that based on the Nth row
SELECT ...
FROM {your table} t
CROSS APPLY dbo.DelimitedSplit8K({your column}, {delimiter}) As d
WHERE d.ItemNumber = {item number you want}
-- Here is what the results from the splitter would look like:
Select * From dbo.DelimitedSplit8K('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z', ',') dsk
Sorry, I'm getting an 'Invalid object name' error when calling the DelimitedSplit8K function. It seems that the function is not found in my SQL Server 2008 installation. How do I fix this problem?