sql - How do I split a string so I can access item x?


Translate

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"?


Toutes les réponses
  • Translate

    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
    

  • Translate

    I don't believe SQL Server has a built-in split function, so other than a UDF, the only other answer I know is to hijack the PARSENAME function:

    SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 
    

    PARSENAME takes a string and splits it on the period character. It takes a number as its second argument, and that number specifies which segment of the string to return (working from back to front).

    SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello
    

    Obvious problem is when the string already contains a period. I still think using a UDF is the best way...any other suggestions?


  • Translate

    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.


  • Translate

    Most of the solutions here use while loops or recursive CTEs. A set-based approach will be superior, I promise:

    CREATE FUNCTION [dbo].[SplitString]
        (
            @List NVARCHAR(MAX),
            @Delim VARCHAR(255)
        )
        RETURNS TABLE
        AS
            RETURN ( SELECT [Value] FROM 
              ( 
                SELECT 
                  [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
                  CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
                FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
                  FROM sys.all_objects) AS x
                  WHERE Number <= LEN(@List)
                  AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
              ) AS y
            );
    

    More on split functions, why (and proof that) while loops and recursive CTEs don't scale, and better alternatives, if splitting strings coming from the application layer:

    On SQL Server 2016 or above, though, you should look at STRING_SPLIT() and STRING_AGG():


  • Translate

    You can leverage a Number table to do the string parsing.

    Create a physical numbers table:

        create table dbo.Numbers (N int primary key);
        insert into dbo.Numbers
            select top 1000 row_number() over(order by number) from master..spt_values
        go
    

    Create test table with 1000000 rows

        create table #yak (i int identity(1,1) primary key, array varchar(50))
    
        insert into #yak(array)
            select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
        go
    

    Create the function

        create function [dbo].[ufn_ParseArray]
            (   @Input      nvarchar(4000), 
                @Delimiter  char(1) = ',',
                @BaseIdent  int
            )
        returns table as
        return  
            (   select  row_number() over (order by n asc) + (@BaseIdent - 1) [i],
                        substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
                from    dbo.Numbers
                where   n <= convert(int, len(@Input)) and
                        substring(@Delimiter + @Input, n, 1) = @Delimiter
            )
        go
    

    Usage (outputs 3mil rows in 40s on my laptop)

        select * 
        from #yak 
        cross apply dbo.ufn_ParseArray(array, ',', 1)
    

    cleanup

        drop table dbo.Numbers;
        drop function  [dbo].[ufn_ParseArray]
    

    Performance here is not amazing, but calling a function over a million row table is not the best idea. If performing a string split over many rows I would avoid the function.


  • Translate

    This question is not about a string split approach, but about how to get the nth element.

    All answers here are doing some kind of string splitting using recursion, CTEs, multiple CHARINDEX, REVERSE and PATINDEX, inventing functions, call for CLR methods, number tables, CROSS APPLYs ... Most answers cover many lines of code.

    But - if you really want nothing more than an approach to get the nth element - this can be done as real one-liner, no UDF, not even a sub-select... And as an extra benefit: type safe

    Get part 2 delimited by a space:

    DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
    SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')
    

    Of course you can use variables for delimiter and position (use sql:column to retrieve the position directly from a query's value):

    DECLARE @dlmt NVARCHAR(10)=N' ';
    DECLARE @pos INT = 2;
    SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')
    

    If your string might include forbidden characters (especially one among &><), you still can do it this way. Just use FOR XML PATH on your string first to replace all forbidden characters with the fitting escape sequence implicitly.

    It's a very special case if - additionally - your delimiter is the semicolon. In this case I replace the delimiter first to '#DLMT#', and replace this to the XML tags finally:

    SET @input=N'Some <, > and &;Other äöü@€;One more';
    SET @dlmt=N';';
    SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');
    

    UPDATE for SQL-Server 2016+

    Regretfully the developers forgot to return the part's index with STRING_SPLIT. But, using SQL-Server 2016+, there is JSON_VALUE and OPENJSON.

    With JSON_VALUE we can pass in the position as the index' array.

    For OPENJSON the documentation states clearly:

    When OPENJSON parses a JSON array, the function returns the indexes of the elements in the JSON text as keys.

    A string like 1,2,3 needs nothing more than brackets: [1,2,3].
    A string of words like this is an example needs to be ["this","is","an","example"].
    These are very easy string operations. Just try it out:

    DECLARE @str VARCHAR(100)='Hello John Smith';
    DECLARE @position INT = 2;
    
    --We can build the json-path '$[1]' using CONCAT
    SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));
    

    --See this for a position safe string-splitter (zero-based):

    SELECT  JsonArray.[key] AS [Position]
           ,JsonArray.[value] AS [Part]
    FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray
    

    In this post I tested various approaches and found, that OPENJSON is really fast. Even much faster than the famous "delimitedSplit8k()" method...


  • Translate

    Here is a UDF which will do it. It will return a table of the delimited values, haven't tried all scenarios on it but your example works fine.

    
    CREATE FUNCTION SplitString 
    (
        -- Add the parameters for the function here
        @myString varchar(500),
        @deliminator varchar(10)
    )
    RETURNS 
    @ReturnTable TABLE 
    (
        -- Add the column definitions for the TABLE variable here
        [id] [int] IDENTITY(1,1) NOT NULL,
        [part] [varchar](50) NULL
    )
    AS
    BEGIN
            Declare @iSpaces int
            Declare @part varchar(50)
    
            --initialize spaces
            Select @iSpaces = charindex(@deliminator,@myString,0)
            While @iSpaces > 0
    
            Begin
                Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
    
                Insert Into @ReturnTable(part)
                Select @part
    
        Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
    
    
                Select @iSpaces = charindex(@deliminator,@myString,0)
            end
    
            If len(@myString) > 0
                Insert Into @ReturnTable
                Select @myString
    
        RETURN 
    END
    GO
    

    You would call it like this:

    
    Select * From SplitString('Hello John Smith',' ')
    

    Edit: Updated solution to handle delimters with a len>1 as in :

    
    select * From SplitString('Hello**John**Smith','**')
    

  • Translate

    Here I post a simple way of solution

    CREATE FUNCTION [dbo].[split](
              @delimited NVARCHAR(MAX),
              @delimiter NVARCHAR(100)
            ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
            AS
            BEGIN
              DECLARE @xml XML
              SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    
              INSERT INTO @t(val)
              SELECT  r.value('.','varchar(MAX)') as item
              FROM  @xml.nodes('/t') as records(r)
              RETURN
            END
    


    Execute the function like this

      select * from dbo.split('Hello John Smith',' ')
    

  • Translate

    In my opinion you guys are making it way too complicated. Just create a CLR UDF and be done with it.

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.Collections.Generic;
    
    public partial class UserDefinedFunctions {
      [SqlFunction]
      public static SqlString SearchString(string Search) {
        List<string> SearchWords = new List<string>();
        foreach (string s in Search.Split(new char[] { ' ' })) {
          if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) {
            SearchWords.Add(s);
          }
        }
    
        return new SqlString(string.Join(" OR ", SearchWords.ToArray()));
      }
    };
    

  • Translate

    What about using string and values() statement?

    DECLARE @str varchar(max)
    SET @str = 'Hello John Smith'
    
    DECLARE @separator varchar(max)
    SET @separator = ' '
    
    DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))
    
    SET @str = REPLACE(@str, @separator, '''),(''')
    SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' 
    
    INSERT INTO @Splited
    EXEC(@str)
    
    SELECT * FROM @Splited
    

    Result-set achieved.

    id  item
    1   Hello
    2   John
    3   Smith
    

  • Translate

    I use the answer of frederic but this did not work in SQL Server 2005

    I modified it and I'm using select with union all and it works

    DECLARE @str varchar(max)
    SET @str = 'Hello John Smith how are you'
    
    DECLARE @separator varchar(max)
    SET @separator = ' '
    
    DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
    
    SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
    SET @str = ' SELECT  ''' + @str + '''  ' 
    
    INSERT INTO @Splited
    EXEC(@str)
    
    SELECT * FROM @Splited
    

    And the result-set is:

    id  item
    1   Hello
    2   John
    3   Smith
    4   how
    5   are
    6   you
    

  • Translate

    This pattern works fine and you can generalize

    Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
                              ^^^^^                                   ^^^^^     ^^^^
    

    note FIELD, INDEX and TYPE.

    Let some table with identifiers like

    sys.message.1234.warning.A45
    sys.message.1235.error.O98
    ....
    

    Then, you can write

    SELECT Source         = q.value('(/n[1])', 'varchar(10)'),
           RecordType     = q.value('(/n[2])', 'varchar(20)'),
           RecordNumber   = q.value('(/n[3])', 'int'),
           Status         = q.value('(/n[4])', 'varchar(5)')
    FROM   (
             SELECT   q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
             FROM     some_TABLE
           ) Q
    

    splitting and casting all parts.


  • Translate

    If your database has compatibility level of 130 or higher then you can use the STRING_SPLIT function along with OFFSET FETCH clauses to get the specific item by index.

    To get the item at index N (zero based), you can use the following code

    SELECT value
    FROM STRING_SPLIT('Hello John Smith',' ')
    ORDER BY (SELECT NULL)
    OFFSET N ROWS
    FETCH NEXT 1 ROWS ONLY
    

    To check the compatibility level of your database, execute this code:

    SELECT compatibility_level  
    FROM sys.databases WHERE name = 'YourDBName';
    

  • Translate

    I was looking for the solution on net and the below works for me. Ref.

    And you call the function like this :

    SELECT * FROM dbo.split('ram shyam hari gopal',' ')
    

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       
    RETURNS @temptable TABLE (items VARCHAR(8000))       
    AS       
    BEGIN       
        DECLARE @idx INT       
        DECLARE @slice VARCHAR(8000)        
        SELECT @idx = 1       
        IF len(@String)<1 OR @String IS NULL  RETURN       
        WHILE @idx!= 0       
        BEGIN       
            SET @idx = charindex(@Delimiter,@String)       
            IF @idx!=0       
                SET @slice = LEFT(@String,@idx - 1)       
            ELSE       
                SET @slice = @String       
            IF(len(@slice)>0)  
                INSERT INTO @temptable(Items) VALUES(@slice)       
            SET @String = RIGHT(@String,len(@String) - @idx)       
            IF len(@String) = 0 break       
        END   
        RETURN       
    END
    

  • Translate

    Yet another get n'th part of string by delimeter function:

    create function GetStringPartByDelimeter (
        @value as nvarchar(max),
        @delimeter as nvarchar(max),
        @position as int
    ) returns NVARCHAR(MAX) 
    AS BEGIN
        declare @startPos as int
        declare @endPos as int
        set @endPos = -1
        while (@position > 0 and @endPos != 0) begin
            set @startPos = @endPos + 1
            set @endPos = charindex(@delimeter, @value, @startPos)
    
            if(@position = 1) begin
                if(@endPos = 0)
                    set @endPos = len(@value) + 1
    
                return substring(@value, @startPos, @endPos - @startPos)
            end
    
            set @position = @position - 1
        end
    
        return null
    end
    

    and the usage:

    select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)
    

    which returns:

    c
    

  • Translate

    Try this:

    CREATE function [SplitWordList]
    (
     @list varchar(8000)
    )
    returns @t table 
    (
     Word varchar(50) not null,
     Position int identity(1,1) not null
    )
    as begin
      declare 
        @pos int,
        @lpos int,
        @item varchar(100),
        @ignore varchar(100),
        @dl int,
        @a1 int,
        @a2 int,
        @z1 int,
        @z2 int,
        @n1 int,
        @n2 int,
        @c varchar(1),
        @a smallint
      select 
        @a1 = ascii('a'),
        @a2 = ascii('A'),
        @z1 = ascii('z'),
        @z2 = ascii('Z'),
        @n1 = ascii('0'),
        @n2 = ascii('9')
      set @ignore = '''"'
      set @pos = 1
      set @dl = datalength(@list)
      set @lpos = 1
      set @item = ''
      while (@pos <= @dl) begin
        set @c = substring(@list, @pos, 1)
        if (@ignore not like '%' + @c + '%') begin
          set @a = ascii(@c)
          if ((@a >= @a1) and (@a <= @z1))  
            or ((@a >= @a2) and (@a <= @z2))
            or ((@a >= @n1) and (@a <= @n2))
          begin
            set @item = @item + @c
          end else if (@item > '') begin
            insert into @t values (@item)
            set @item = ''
          end
        end 
        set @pos = @pos + 1
      end
      if (@item > '') begin
        insert into @t values (@item)
      end
      return
    end
    

    Test it like this:

    select * from SplitWordList('Hello John Smith')
    

  • Translate

    The following example uses a recursive CTE

    Update 18.09.2013

    CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
    RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
    AS
    BEGIN
    ;WITH cte AS
     (
      SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,
             CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval, 
             1 AS [level]
      UNION ALL
      SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
             CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
             [level] + 1
      FROM cte
      WHERE stval != ''
      )
      INSERT @returns
      SELECT REPLACE(val, ' ','' ) AS val, [level]
      FROM cte
      WHERE val > ''
      RETURN
    END
    

    Demo on SQLFiddle


  • Translate
    
    
        Alter Function dbo.fn_Split
        (
        @Expression nvarchar(max),
        @Delimiter  nvarchar(20) = ',',
        @Qualifier  char(1) = Null
        )
        RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
        AS
        BEGIN
           /* USAGE
                Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null)
                Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"')
                Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"')
           */
    
           -- Declare Variables
           DECLARE
              @X     xml,
              @Temp  nvarchar(max),
              @Temp2 nvarchar(max),
              @Start int,
              @End   int
    
           -- HTML Encode @Expression
           Select @Expression = (Select @Expression For XML Path(''))
    
           -- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
           While PATINDEX('%' + @Qualifier + '%', @Expression) > 0 AND Len(IsNull(@Qualifier, '')) > 0
           BEGIN
              Select
                 -- Starting character position of @Qualifier
                 @Start = PATINDEX('%' + @Qualifier + '%', @Expression),
                 -- @Expression starting at the @Start position
                 @Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),
                 -- Next position of @Qualifier within @Expression
                 @End = PATINDEX('%' + @Qualifier + '%', @Temp) - 1,
                 -- The part of Expression found between the @Qualifiers
                 @Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
                 -- New @Expression
                 @Expression = REPLACE(@Expression,
                                       @Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End,
                                       Replace(@Temp2, @Delimiter, '|||***|||')
                               )
           END
    
           -- Replace all occurences of @Delimiter within @Expression with '</fn_Split><fn_Split>'
           -- And convert it to XML so we can select from it
           SET
              @X = Cast('<fn_Split>' +
                        Replace(@Expression, @Delimiter, '</fn_Split><fn_Split>') +
                        '</fn_Split>' as xml)
    
           -- Insert into our returnable table replacing '|||***|||' back to @Delimiter
           INSERT @Results
           SELECT
              "Value" = LTRIM(RTrim(Replace(C.value('.', 'nvarchar(max)'), '|||***|||', @Delimiter)))
           FROM
              @X.nodes('fn_Split') as X(C)
    
           -- Return our temp table
           RETURN
        END
    
    

  • Translate

    I know it's an old Question, but i think some one can benefit from my solution.

    select 
    SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
    ,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
        ,1
        ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
    ,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
        ,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
        ,LEN(column_name))
    from table_name
    

    SQL FIDDLE

    Advantages:

    • It separates all the 3 sub-strings deliminator by ' '.
    • One must not use while loop, as it decreases the performance.
    • No need to Pivot as all the resultant sub-string will be displayed in one Row

    Limitations:

    • One must know the total no. of spaces (sub-string).

    Note: the solution can give sub-string up to to N.

    To overcame the limitation we can use the following ref.

    But again the above solution can't be use in a table (Actaully i wasn't able to use it).

    Again i hope this solution can help some-one.

    Update: In case of Records > 50000 it is not advisable to use LOOPS as it will degrade the Performance


  • Translate

    Almost all the other answers split code are replacing the string being split which wastes CPU cycles and performs unnecessary memory allocations.

    I cover a much better way to do a string split here: http://www.digitalruby.com/split-string-sql-server/

    Here is the code:

    SET NOCOUNT ON
    
    -- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
    DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
    DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
    DECLARE @SplitEndPos int
    DECLARE @SplitValue nvarchar(MAX)
    DECLARE @SplitDelim nvarchar(1) = '|'
    DECLARE @SplitStartPos int = 1
    
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
    
    WHILE @SplitEndPos > 0
    BEGIN
        SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
        INSERT @SplitStringTable (Value) VALUES (@SplitValue)
        SET @SplitStartPos = @SplitEndPos + 1
        SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
    END
    
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
    INSERT @SplitStringTable (Value) VALUES(@SplitValue)
    
    SET NOCOUNT OFF
    
    -- You can select or join with the values in @SplitStringTable at this point.
    

  • Translate

    You can split a string in SQL without needing a function:

    DECLARE @bla varchar(MAX)
    SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'
    
    -- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
    SELECT 
        x.XmlCol.value('.', 'varchar(36)') AS val 
    FROM 
    (
        SELECT 
        CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
    ) AS b 
    CROSS APPLY b.RawXml.nodes('e') x(XmlCol);
    

    If you need to support arbitrary strings (with xml special characters)

    DECLARE @bla NVARCHAR(MAX)
    SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'
    
    -- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
    SELECT 
        x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
    FROM 
    (
        SELECT 
        CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
    ) AS b 
    CROSS APPLY b.RawXml.nodes('e') x(XmlCol); 
    

  • Translate

    Pure set-based solution using TVF with recursive CTE. You can JOIN and APPLY this function to any dataset.

    create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
    returns table
    as return
    with r as (
        select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
        union all
        select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
        , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
        , [no] + 1 [no]
        from r where value > '')
    
    select ltrim(x) [value], [no] [index] from r where x is not null;
    go
    

    Usage:

    select *
    from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
    where [index] = 1;
    

    Result:

    value   index
    -------------
    John    1
    

  • Translate

    Starting with SQL Server 2016 we string_split

    DECLARE @string varchar(100) = 'Richard, Mike, Mark'
    
    SELECT value FROM string_split(@string, ',')
    

  • Translate

    A modern approach using STRING_SPLIT, requires SQL Server 2016 and above.

    DECLARE @string varchar(100) = 'Hello John Smith'
    
    SELECT
        ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
        value
    FROM string_split(@string, ' ')
    

    Result:

    RowNr   value
    1       Hello
    2       John
    3       Smith
    

    Now it is possible to get th nth element from the row number.


  • Translate

    Aaron Bertrand's answer is great, but flawed. It doesn't accurately handle a space as a delimiter (as was the example in the original question) since the length function strips trailing spaces.

    The following is his code, with a small adjustment to allow for a space delimiter:

    CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( SELECT [Value] FROM 
          ( 
            SELECT 
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
              FROM sys.all_objects) AS x
              WHERE Number <= LEN(@List)
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
          ) AS y
        );
    

  • Translate

    Here is a function that will accomplish the question's goal of splitting a string and accessing item X:

    CREATE FUNCTION [dbo].[SplitString]
    (
       @List       VARCHAR(MAX),
       @Delimiter  VARCHAR(255),
       @ElementNumber INT
    )
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    
           DECLARE @inp VARCHAR(MAX)
           SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))
    
           DECLARE @xml XML
           SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'
    
           DECLARE @ret VARCHAR(MAX)
           SET @ret = (SELECT
                  el = split.el.value('.','varchar(max)')
           FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))
    
           RETURN @ret
    
    END
    

    Usage:

    SELECT dbo.SplitString('Hello John Smith', ' ', 2)
    

    Result:

    John
    

  • Translate

    SIMPLE SOLUTION FOR PARSING FIRST AND LAST NAME

    DECLARE @Name varchar(10) = 'John Smith'
    
    -- Get First Name
    SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))
    
    -- Get Last Name
    SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))
    

    In my case (and in many others it seems...), I have a list of first and last names separated by a single space. This can be used directly inside a select statement to parse first and last name.

    -- i.e. Get First and Last Name from a table of Full Names
    SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
    SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
    From FullNameTable
    

  • Translate

    I know its late, but I recently had this requirement and came up with the below code. I don't have a choice to use User defined function. Hope this helps.

    SELECT 
        SUBSTRING(
                    SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
                            ),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')
                )
    

  • Translate

    Well, mine isn't all that simpler, but here is the code I use to split a comma-delimited input variable into individual values, and put it into a table variable. I'm sure you could modify this slightly to split based on a space and then to do a basic SELECT query against that table variable to get your results.

    -- Create temporary table to parse the list of accounting cycles.
    DECLARE @tblAccountingCycles table
    (
        AccountingCycle varchar(10)
    )
    
    DECLARE @vchAccountingCycle varchar(10)
    DECLARE @intPosition int
    
    SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
    SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
    
    IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
    BEGIN
        WHILE @intPosition > 0
        BEGIN
            SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
            IF @vchAccountingCycle <> ''
            BEGIN
                INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
            END
            SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
            SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
        END
    END
    

    The concept is pretty much the same. One other alternative is to leverage the .NET compatibility within SQL Server 2005 itself. You can essentially write yourself a simple method in .NET that would split the string and then expose that as a stored procedure/function.


  • Translate

    This is something I did in order to get a specific token in a string. (Tested in MSSQL 2008)

    First, creating the following functions: (found in: here

    CREATE FUNCTION dbo.SplitStrings_Moden
    (
       @List NVARCHAR(MAX),
       @Delimiter NVARCHAR(255)
    )
    RETURNS TABLE
    WITH SCHEMABINDING AS
    RETURN
      WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                             UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                             UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
           E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
           E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
           E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
           cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                             ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
           cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                             WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
      SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
        FROM cteStart s;
    

    and

    create FUNCTION dbo.getToken
    (
    @List NVARCHAR(MAX),
    @Delimiter NVARCHAR(255),
    @Pos int
    )
    RETURNS varchar(max)
    as 
    begin
    declare @returnValue varchar(max);
    select @returnValue = tbl.Item from (
    select ROW_NUMBER() over (order by (select null)) as id, * from dbo.SplitStrings_Moden(@List, @Delimiter)
    ) as tbl
    where tbl.id = @Pos
    return @returnValue
    end
    

    then you can use it like that:

    select dbo.getToken('1111_2222_3333_', '_', 1)
    

    which return 1111