SQLTeam.com | Weblogs | Forums

Help with Function to Return the Nth Element of a Delimited String

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?

Thanks very much! :slight_smile:

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

Use a high-performance splitter that counts each entry, such as DelimitedSplit8K, and then just specify the occur# on a WHERE clause.

Thanks for your replies!

Sorry for my ignorance but I don't know anything about the splitter function that you mention. A sample code would really help! :slight_smile:

Here is a link to the article for one of the better splitter functions: http://www.sqlservercentral.com/articles/Tally+Table/72993/

The code would be this:

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

Thanks, Jeff!

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?

OK, I got it after reading the article you linked to, which I didn't read at first. My bad. :slight_smile:

Your solution works great. Thank you so much!!

It is a very handy procedure to have available - you will find all sorts of uses for it.