How do I split a string so I can access item x *

Question

Using SQL Server 2005, how do I split a string so I can access item x?

For example, take the string "Hello John Smith". How can I split the string by a space and access the item at index 1 which should return "John"?

Answer

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

U can use this simple logic -

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%',@products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products, 0, PATINDEX('%|%',@products))
        SELECT @individual

        SET @products = SUBSTRING(@products, LEN(@individual + '|') + 1,
                                                     LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/2647/" >How do I split a string so I can access item x< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.