For this type of problem I typically use a UDF. The one I'm providing here will return a table for each record. If you are only using it for a specific purpose you could certainly modify it to just return the full word for a given location. IE: 1st, 2nd, or last word.

I did just that, and this is what I came up with:

GO

DROP FUNCTION dbo.f_ParseDelimitedListPart

GO

CREATE FUNCTION dbo.f_ParseDelimitedListPart

(

@ID int, -- Used so we can link the resulting table in a query to some value

@delimitedList nvarchar(3000), -- The list of items to parse out.

@Delimiter nvarchar(10) = ',' , -- The delimiter used. Defaults to a comma.

@listpart int

)

RETURNS nvarchar(3000)

BEGIN

DECLARE @FieldValue nvarchar(260), @string nvarchar(3000)

DECLARE @loopCnt int, @delimLength int

SET @loopCnt = 0

SET @delimLength = (SELECT Len(@Delimiter))

SET @string = ''

WHILE CharIndex(@Delimiter, @delimitedList) > 0

BEGIN

SET @loopCnt = @loopCnt + 1

SET @FieldValue = LTrim(RTrim(SUBSTRING(@delimitedList, 1, charIndex(@Delimiter, @delimitedList)-1)))

IF @listpart = @loopCnt

RETURN @FieldValue

SET @delimitedList = LTrim(RTrim(SUBSTRING(@delimitedList, (charIndex(@Delimiter, @delimitedList) + @delimLength), Len(@delimitedList))))

END

IF @listpart = @loopCnt + 1

SET @string = @delimitedList

ELSE

SET @string = ''

RETURN @string

END

-- EXAMPLES

/*

Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges', ',', 1)

Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 2)

Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 3)

Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 4)

Select dbo.f_ParseDelimitedListPart(1, 'Apples,Oranges,Bananas', ',', 5)

Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges', '//', 1)

Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges//Bananas', '//', 2)

Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges//Bananas', '//', 3)

Select dbo.f_ParseDelimitedListPart(1, 'Apples//Oranges//Bananas', '//', 4)

*/