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

Transact-SQL
1

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:

2

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

3

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

4

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:

5

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:

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
6

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?

7

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!!

8

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