Categories
split sql sql-server tsql

How do I split a delimited string so I can access individual items?

523

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

Take a string “Hello John Smith”. How can I split the string by space and access the item at index 1 which should return “John”?

7

194

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

You 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

8

  • 1

    why SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText))) and not SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( @p_SourceText)?

    – Beth

    Sep 29, 2010 at 15:13


  • 12

    @GateKiller This solution does not support Unicode & it uses hard coded numeric(18,3) which doesn’t make it a viable “reusable” function.

    Mar 18, 2011 at 13:55

  • 4

    This works but allocates a lot of memory and wastes CPU.

    – jjxtra

    May 26, 2015 at 16:56

  • 2

    As of SQL Server 2016, there is now a built-in function STRING_SPLIT that will split a string and return a one-column table result which you can use in a SELECT statement or elsewhere.

    – qJake

    Apr 3, 2017 at 20:24


  • Too bad the guys I work for aren’t on 2016. But, I’ll keep it in mind in case they ever get the lead out of their shoes. Great solution in the interim. I implemented it as a function and and added delimiter as an argument.

    May 2, 2017 at 19:38

194

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

You 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

8

  • 1

    why SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( RTRIM( LTRIM( @p_SourceText))) and not SET @p_SourceText = RTRIM( LTRIM( @p_SourceText)) SET @w_Length = DATALENGTH( @p_SourceText)?

    – Beth

    Sep 29, 2010 at 15:13


  • 12

    @GateKiller This solution does not support Unicode & it uses hard coded numeric(18,3) which doesn’t make it a viable “reusable” function.

    Mar 18, 2011 at 13:55

  • 4

    This works but allocates a lot of memory and wastes CPU.

    – jjxtra

    May 26, 2015 at 16:56

  • 2

    As of SQL Server 2016, there is now a built-in function STRING_SPLIT that will split a string and return a one-column table result which you can use in a SELECT statement or elsewhere.

    – qJake

    Apr 3, 2017 at 20:24


  • Too bad the guys I work for aren’t on 2016. But, I’ll keep it in mind in case they ever get the lead out of their shoes. Great solution in the interim. I implemented it as a function and and added delimiter as an argument.

    May 2, 2017 at 19:38

111

First, create a function (using CTE, common table expression does away with the need for a temp table)

 create function dbo.SplitString 
    (
        @str nvarchar(4000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p-1 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

Then, use it as any table (or modify it to fit within your existing stored proc) like this.

select s 
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1

Update

Previous version would fail for input string longer than 4000 chars. This version takes care of the limitation:

create function dbo.SplitString 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS s
from tokens
);

GO

Usage remains the same.

6

  • 14

    It’s elegant but only works for 100 elements because of the limit of recursion depth.

    – Pking

    Nov 7, 2012 at 15:31

  • 4

    @Pking, no, the default is 100 (to prevent infinite loop). Use MAXRECURSION hint to define number of recursion levels (0 to 32767, 0 is “no limit” – may crush server). BTW, much better answer than PARSENAME, because it’s universal :-). +1

    Mar 14, 2013 at 14:45


  • Adding maxrecursion to this solution keep in mind this question and its answers How to setup the maxrecursion option for a CTE inside a Table-Valued-Function.

    Mar 15, 2013 at 9:03


  • Specifically, reference the answer by Crisfole – his method slows it somewhat, but is simpler than most other options.

    – AHiggins

    Jul 30, 2015 at 18:05

  • minor point but the usage doesn’t remain the same because you changed the column name, so s is no longer defined

    – Tim Abell

    Jun 24, 2016 at 9:45