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.

 

CREATE FUNCTION dbo.f_ParseDelimitedList

    (

    @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        nchar(1) = ','  -- The delimiter used. Defaults to a comma.

    )

RETURNS @tbl TABLE (ID int ,ObjID int , FieldValue nvarchar(260))

BEGIN

    DECLARE

    @CounterID               nvarchar(4)

    , @FieldValue            nvarchar(260)

    DECLARE @tmpTable TABLE (ID int IDENTITY(1,1),ObjID int , FieldValue nvarchar(260))

    WHILE CharIndex(@Delimiter, @delimitedList) > 0

        BEGIN

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

            INSERT INTO @tmpTable (ObjID, FieldValue)

            SELECT @ID, @FieldValue

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

        END

    IF  LTrim(RTrim(@delimitedList)) != ''

    INSERT INTO @tmpTable (ObjID, FieldValue)

    SELECT @ID, @delimitedList

    INSERT INTO @tbl SELECT * FROM @tmpTable

    RETURN

END

/* Usage 

        Select *

          From dbo.f_ParseDelimitedList(1,'Apples,Oranges',',')

*/

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Edited by - gljjr on 11/17/2003 7:23:51 PM