In SQL Server, do you have a table with a column that has several values in it that are separated with a comma? If you have a string with four or fewer elements, you can use the PARSENAME function to separate them. For an example of this please have a look at post called Multiple Genres in Genres Column. This code pulls out the fifth string, which is LMN.

This post is for times when you have at most five or more elements in your string. Why do I say that? The example in this post uses CHARINDEX and SUBSTRING which is more messy that using the PARSENAME function. It’s harder to read.

CHARINDEX

The CHARINDEX() function searches for a substring in a string and returns the position. If the substring is not found, this function returns 0. It returns the first occurrence if there are multiple occurrences. Suppose you want to find the position of the letter t in Matt. The code below returns 3 because the first occurrence of the letter t in Matt is in the third position. SUBSTRING has an optional third parameter, which is where the search will start. The default is 1.

SELECT CHARINDEX('t', 'Matt') AS MatchPosition;

You can search for a string such as “mer”, not just a single character.

SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition; -- 6
SELECT CHARINDEX('t', 'Matt', 3) AS MatchPosition; -- 3
SELECT CHARINDEX('t', 'Matt', 4) AS MatchPosition; -- 4, the first 3 characters are ignored in search
SELECT CHARINDEX('us', 'Customer', 3) AS MatchPosition; -- 0. No match when we start at third character, the s.

A Two Part Example

You can go ahead and copy and paste this code into your non-production server and test that it works. It returns everything after the first comma, even if it encounter more commas asfter the first comma.

declare @s nvarchar(50);
set @s = 'apple,berry'
declare @pos int;
set @pos = CHARINDEX(',', @s); 
select @pos + 1 AS 'Start in position'  -- 7
select substring(@s, @pos + 1, LEN(@s) - @pos + 1) -- berry

Three Part

declare @s nvarchar(50);
set @s = 'apple,berry,cherry'
declare @pos int;
set @pos = CHARINDEX(',', @s, charindex(',', @s) + 1); 
select @pos + 1 AS 'Start in position' -- 13
select substring(@s, @pos + 1, LEN(@s) - @pos + 1) -- cherry

Four Parts

declare @s nvarchar(50);
set @s = 'apple,berry,cherry,date'
declare @pos int;
set @pos = CHARINDEX(',', @s, charindex(',', @s,  charindex(',',  @s) + 1) + 1); 
select @pos + 1 AS 'Start in position' -- 
select substring(@s, @pos + 1, LEN(@s) - @pos + 1) -- date

A Five Part Example

You can go ahead and copy and paste this code into your non-production server and test that it works.

declare @s NVARCHAR(30)
set @s = 'a,e,d,W,LMN';
declare @pos int;  -- position of the comma
set @pos = CHARINDEX(',',@s,(charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1) 
set @pos = @pos + 1;  -- position of the first character 
select @pos AS 'position'
-- I want the fifth group here, which is LMN
-- it starts 9th position which is the letter L
select substring(@s, @pos, LEN(@s) - @pos + 1)

Go ahead and change the length of some of the elements in the string to be sure it still works correctly. You could try something like the following.

set @s = 'ab,esdfgtr fgbvfg,d,W,LMNQZ';

A Six Part Example

If we want the sixth item is our comma delimited string we would need to add another CHARINDEX in our @pos formula.

declare @s NVARCHAR(30)
set @s = 'a,e,d,W5,LMN,QW';
declare @pos int;  -- position of the comma
set @pos = charindex(',',@s,(CHARINDEX(',',@s,(charindex(',',@s,charindex(',',@s,charindex(',',@s)+1)+1)+1)+1)+1)) 
set @pos = @pos + 1;  -- position of the first character 
select @pos AS 'position'
-- I want the sixth group here, which is QW
-- it starts 14th position which is the letter Q
select substring(@s, @pos, LEN(@s) - @pos + 1)