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

2 upvote
  flag
4 upvote
  flag
built-in as of sql server 2016 msdn.microsoft.com/en-us/library/mt684588.aspx – Tim Abell
2 upvote
  flag
The highest answers here are - at least for me - quite old fashioned and rather out-dated. Procedural locig, loops, recursions, CLR, functions, many lines of code... It might be interesting to read the "active" answers to find more up-to-date approaches. – Shnugo

37 Answers 11

up vote 171 down vote accepted

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
1 upvote
  flag
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
11 upvote
  flag
@GateKiller This solution does not support Unicode & it uses hard coded numeric(18,3) which doesn't make it a viable "reusable" function. – Filip De Vos
2 upvote
  flag
This works but allocates a lot of memory and wastes CPU. – jjxtra
upvote
  flag
I've added a new answer, which I guess is the fastest.. – Ramazan Binarbasi
7 upvote
  flag
haha "simple" ... – Niels Abildgaard
upvote
  flag
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
upvote
  flag
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. – Brandon Griffin

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.

upvote
  flag
an example of doing it in .NET (CLR Procedures/functions) can be found here cstruter.com/blog/260 – cstruter

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','**')
upvote
  flag
Didn't work for select * from dbo.ethos_SplitString_fn('guy,wicks,was here',',') id part ----------- -------------------------------------------------- 1 guy 2 wick – Guy
2 upvote
  flag
watch out with len() as it'll not return correct number if its argument has trailing spaces., e.g. len(' - ') = 2. – Rory
upvote
  flag
Doesn't work on: select * from dbo.SplitString('foo,foo test,,,,foo',',') – cbp
1 upvote
  flag
Fix for cbp.. Select @myString = substring(@mystring,@iSpaces + len(@deliminator),len(@myString) - charindex(@deliminator,@myString,0)) – Alxwest

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')
upvote
  flag
I've gone through it & it is perfectly like what I want! even I can also customize it for ignoring special characters that I choose! – Vikas

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?

96 upvote
  flag
Thanks Saul...I should point out that this solution is really a bad solution for real development. PARSENAME only expects four parts, so using a string with more than four parts causes it to return NULL. The UDF solutions are obviously better. – Nathan Bedford
29 upvote
  flag
This is a great hack, and also makes me weep that something like this is necessary for something so friggin simple in real languages. – Factor Mystic
32 upvote
  flag
To make the indexes work in the "right" way, that is, starting at 1, i've hijacked your hijack with REVERSE: REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1)) -- Returns Hello – NothingsImpossible
3 upvote
  flag
@FactorMystic First Normal Form requires that you not put multiple values in a single field. It's literally the first rule of an RDBMS. A SPLIT() function is not supplied because it encourages poor database design, and the database will never be optimized to use data stored in this format. The RDBMS is not obligated to help developers do stupid things that it has been designed not to handle. The correct answer will always be "Normalize your database like we told you 40 years ago." Neither SQL nor the RDBMS are to blame for poor design. – Bacon Bits
upvote
  flag
This is uber cool. It just so happens that the data i'm working with has four elements – MrBliz
upvote
  flag
+1 can always concoct a quick and dirty working solution for >4 tokens with a combination of reverse(), charindex(), substring() – hello_earth
3 upvote
  flag
@BaconBits while I agree in theory, in practice tools like this are useful when normalizing a poor design produced by someone who came before you. – Tim Abell
upvote
  flag
When the number of splits in the string varies from left to right, then the @NothingsImpossible route is the way to go . – wwmbes
upvote
  flag
@NothingsImpossible The performance of the REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1)) solution is excellent and the 4 field limitation fits my problem. Thanks!!! – wwmbes
upvote
  flag
@hello_earth I'd love to see what you come up with. Please show us. – wwmbes
upvote
  flag
@wwmbes i posted a separate answer - it's very quick and dirty and ugly, and stems from the same principles but anyway, since you asked. cheers – hello_earth
1 upvote
  flag
For awareness: Since parsename function is designed to return database identifiers, the value returned is limited to 128 characters (a sysname data type, which corresponds to nvarchar(128) ). If it goes beyond this, a NULL will be returned instead. – Kyle Weller

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.

14 upvote
  flag
It's elegant but only works for 100 elements because of the limit of recursion depth. – Pking
3 upvote
  flag
@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 – Michał Powaga
upvote
  flag
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. – Michał Powaga
upvote
  flag
Specifically, reference the answer by Crisfole - his method slows it somewhat, but is simpler than most other options. – AHiggins
upvote
  flag
minor point but the usage doesn't remain the same because you changed the column name, so s is no longer defined – Tim Abell

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.

2 upvote
  flag
The best solution IMO, the others have some kind of limitation.. this is fast and can parse long strings with many elements. – Pking
upvote
  flag
Why do you order n descending? If there where three items, and we started numbering at 1, then the first item will be number 3, and the last will be number 1. Wouldn't it give more intuitive results if the desc were removed? – hatchet
1 upvote
  flag
Agreed, would be more intuitive in the asc direction. I was following parsename() convention which uses desc – Nathan Skerl
3 upvote
  flag
some explanation as to how this works would be great – Tim Abell
upvote
  flag
In a test on 100 million rows of up to 3 fields to parse, ufn_ParseArray did not finish after 25 minutes, while REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1)) from @NothingsImpossible completed in 1.5min. @hello_earth How would your solution compare on longer strings with more than 4 fields? – wwmbes
upvote
  flag
On further investigation, when the @NothingsImpossible version is embedded in a function and used from there, it performs 25 times worse than when used directly in a query. Can anyone comment on why? – wwmbes
upvote
  flag
@wwmbes Try using a physical number table with a clustered index. The usage of master..spt_values is just for illustration – Nathan Skerl
upvote
  flag
@wwmbes added a physical number table example – Nathan Skerl

No code, but read the definitive article on this. All solutions in other answers are flavours of the ones listed in this article: Arrays and Lists in SQL Server 2005 and Beyond

Personally, I've used a Numbers table solution most often because it suits what I have to do...

1 upvote
  flag
Perhaps you should expand on the link, and show your solution based on a numbers table. The answer as it is now is just a link to answers elsewhere, which isn't a real answer (anymore perhaps). Link only answers are discouraged also because link-rot (but you know that). – TT.
1 upvote
  flag
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review – Nilu
upvote
  flag
link rot from erland? i doubt it. 7 years later still there – gbn

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
upvote
  flag
You can't easily access the Nth item using this function. – Björn Lindqvist

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()));
  }
};
16 upvote
  flag
I guess this is too much complicated, because I need to have Visual Studio, then enable CLR on the server, then create and compile the project, and finally add the assemblies to the database, in order to use it. But still is an interesting answer. – Guillermo Gutiérrez
2 upvote
  flag
@guillegr123, it doesn't have to be complicated. You can just download and install (for free!), SQL#, which is a library of SQLCLR functions and procs. You can get it from SQLsharp.com . Yes, I am the author but String_Split is included in the Free version. – Solomon Rutzky

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

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',' ')
upvote
  flag
I liked this solution. Expanded it to return a scalar value based on the specified column within the results. – Alan
upvote
  flag
it's a great solution if you are concerned with performance... – Andre Figueiredo

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
1 upvote
  flag
i used your answer but did not work, but i modified and this worked with union all, i am using sql 2005 – angel

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

Here's my solution that may help someone. Modification of Jonesinator's answer above.

If I have a string of delimited INT values and want a table of INTs returned (Which I can then join on). e.g. '1,20,3,343,44,6,8765'

Create a UDF:

IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL
    DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];
GO

CREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX),                 @Delimiter CHAR(1))

RETURNS @table TABLE 
(
    Value INT NOT NULL
)
AS 
BEGIN
DECLARE @Pattern NVARCHAR(3)
SET @Pattern = '%' + @Delimiter + '%'
DECLARE @Value NVARCHAR(MAX)

WHILE LEN(@String) > 0
    BEGIN
        IF PATINDEX(@Pattern, @String) > 0
        BEGIN
            SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))
            INSERT INTO @table (Value) VALUES (@Value)

            SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))
        END
        ELSE
        BEGIN
            -- Just the one value.
            INSERT INTO @table (Value) VALUES (@String)
            RETURN
        END
    END

RETURN
END
GO

Then get the table results:

SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')

1
20
3
343
44
6
8765

And in a join statement:

SELECT [ID], [FirstName]
FROM [User] u
JOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]

1    Elvis
20   Karen
3    David
343  Simon
44   Raj
6    Mike
8765 Richard

If you want to return a list of NVARCHARs instead of INTs then just change the table definition:

RETURNS @table TABLE 
(
    Value NVARCHAR(MAX) NOT NULL
)

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

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
upvote
  flag
This is really great i've ever seen in sql stuff, it worked for my job and i appreciate that, thanks ! – Abdurrahman I.
upvote
  flag
I got really excited when I saw this because it looked so clean and easy to understand, but unfortunately you can't put this inside a UDF because of the EXEC. EXEC implicitly calls a stored procedure, and you can't use stored procedures in UDFs. – Kristen Hammack


    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

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:

http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

http://www.sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx

1 upvote
  flag
Best answer, IMHO. In some of other answers there is the issue of SQL recursion limit of 100, but not in this case. Very fast and very simple implementation. Where is the +2 button? – T-moty
upvote
  flag
Thanks to Aaron; But can someone explain why if I pass varchar (not varchar(max)) as the argument this function returns empty list? like declare @list varchar = 'something'; select from dbo.SplitString(@list, ';'); – Mikhail Boyarsky
upvote
  flag
@Mikhail Because varchar without length can either be varchar(30) or varchar(1) depending on context. Don't try to understand that problem - just don't use that syntax. Ever. – Aaron Bertrand
3 upvote
  flag
I tried this function verbatim with the usage: select * from DBO.SplitString('Hello John smith', ' '); and the output produced was: Value Hello ello llo lo o John ohn hn n smith mith ith th h – wwmbes
upvote
  flag
@wwmbes Try using a delimiter other than space. Many functions are going to have the issue that trailing spaces are dropped. – Aaron Bertrand
1 upvote
  flag
@AaronBertrand The original problem posted by GateKiller involves a space delimiter. – wwmbes

Recursive CTE solution with server pain, test it

MS SQL Server 2008 Schema Setup:

create table Course( Courses varchar(100) );
insert into Course values ('Hello John Smith');

Query 1:

with cte as
   ( select 
        left( Courses, charindex( ' ' , Courses) ) as a_l,
        cast( substring( Courses, 
                         charindex( ' ' , Courses) + 1 , 
                         len(Courses ) ) + ' ' 
              as varchar(100) )  as a_r,
        Courses as a,
        0 as n
     from Course t
    union all
      select 
        left(a_r, charindex( ' ' , a_r) ) as a_l,
        substring( a_r, charindex( ' ' , a_r) + 1 , len(a_R ) ) as a_r,
        cte.a,
        cte.n + 1 as n
    from Course t inner join cte 
         on t.Courses = cte.a and len( a_r ) > 0

   )
select a_l, n from cte
--where N = 1

Results:

|    A_L | N |
|--------|---|
| Hello  | 0 |
|  John  | 1 |
| Smith  | 2 |

Here is a SQL UDF that can split a string and grab just a certain piece.

create FUNCTION [dbo].[udf_SplitParseOut]
(
    @List nvarchar(MAX),
    @SplitOn nvarchar(5),
    @GetIndex smallint
)  
returns varchar(1000)
AS  

BEGIN

DECLARE @RtnValue table 
(

    Id int identity(0,1),
    Value nvarchar(MAX)
) 


    DECLARE @result varchar(1000)

    While (Charindex(@SplitOn,@List)>0)
    Begin
        Insert Into @RtnValue (value)
        Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
        Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    End

    Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    select @result = value from @RtnValue where ID = @GetIndex

    Return @result
END

A simple optimized algorithm :

ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50)) 
AS
BEGIN
    DECLARE @PathInd INT
    Set @Text+=@Splitor
    WHILE LEN(@Text) > 0
    BEGIN
        SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
        INSERT INTO  @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
        SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
    END
        RETURN 
END

I've been using vzczc's answer using recursive cte's for some time, but have wanted to update it to handle a variable length separator and also to handle strings with leading and lagging "separators" such as when you have a csv file with records such as:

"Bob","Smith","Sunnyvale","CA"

or when you are dealing with six part fqn's as shown below. I use these extensively for logging of the subject_fqn for auditing, error handling, etc. and parsename only handles four parts:

[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]

Here is my updated version, and thanks to vzczc's for his original post!

select * from [utility].[split_string](N'"this"."string"."gets"."split"."and"."removes"."leading"."and"."trailing"."quotes"', N'"."', N'"', N'"');

select * from [utility].[split_string](N'"this"."string"."gets"."split"."but"."leaves"."leading"."and"."trailing"."quotes"', N'"."', null, null);

select * from [utility].[split_string](N'[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]', N'].[', N'[', N']');

create function [utility].[split_string] ( 
  @input       [nvarchar](max) 
  , @separator [sysname] 
  , @lead      [sysname] 
  , @lag       [sysname]) 
returns @node_list table ( 
  [index]  [int] 
  , [node] [nvarchar](max)) 
  begin 
      declare @separator_length [int]= len(@separator) 
              , @lead_length    [int] = isnull(len(@lead), 0) 
              , @lag_length     [int] = isnull(len(@lag), 0); 
      -- 
      set @input = right(@input, len(@input) - @lead_length); 
      set @input = left(@input, len(@input) - @lag_length); 
      -- 
      with [splitter]([index], [starting_position], [start_location]) 
           as (select cast(@separator_length as [bigint]) 
                      , cast(1 as [bigint]) 
                      , charindex(@separator, @input) 
               union all 
               select [index] + 1 
                      , [start_location] + @separator_length 
                      , charindex(@separator, @input, [start_location] + @separator_length) 
               from   [splitter] 
               where  [start_location] > 0) 
      -- 
      insert into @node_list 
                  ([index],[node]) 
        select [index] - @separator_length                   as [index] 
               , substring(@input, [starting_position], case 
                                                            when [start_location] > 0 
                                                                then 
                                                              [start_location] - [starting_position] 
                                                            else 
                                                              len(@input) 
                                                        end) as [node] 
        from   [splitter]; 
      -- 
      return; 
  end; 
go 

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.

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.

upvote
  flag
This is the only solution here which allows you to cast to specific types, and is moderately efficient (CLR still is most efficient, but this approach handles an 8gb, 10 token, 10M row table in about 9 mins (aws m3 server, 4k iops provisioned drive) – Andrew Hill

while similar to the xml based answer by josejuan, i found that processing the xml path only once, then pivoting was moderately more efficient:

select ID,
    [3] as PathProvidingID,
    [4] as PathProvider,
    [5] as ComponentProvidingID,
    [6] as ComponentProviding,
    [7] as InputRecievingID,
    [8] as InputRecieving,
    [9] as RowsPassed,
    [10] as InputRecieving2
    from
    (
    select id,message,d.* from sysssislog cross apply       ( 
          SELECT Item = y.i.value('(./text())[1]', 'varchar(200)'),
              row_number() over(order by y.i) as rn
          FROM 
          ( 
             SELECT x = CONVERT(XML, '<i>' + REPLACE(Message, ':', '</i><i>') + '</i>').query('.')
          ) AS a CROSS APPLY x.nodes('i') AS y(i)
       ) d
       WHERE event
       = 
       'OnPipelineRowsSent'
    ) as tokens 
    pivot 
    ( max(item) for [rn] in ([3],[4],[5],[6],[7],[8],[9],[10]) 
    ) as data

ran in 8:30

select id,
tokens.value('(/n[3])', 'varchar(100)')as PathProvidingID,
tokens.value('(/n[4])', 'varchar(100)') as PathProvider,
tokens.value('(/n[5])', 'varchar(100)') as ComponentProvidingID,
tokens.value('(/n[6])', 'varchar(100)') as ComponentProviding,
tokens.value('(/n[7])', 'varchar(100)') as InputRecievingID,
tokens.value('(/n[8])', 'varchar(100)') as InputRecieving,
tokens.value('(/n[9])', 'varchar(100)') as RowsPassed
 from
(
    select id, Convert(xml,'<n>'+Replace(message,'.','</n><n>')+'</n>') tokens
         from sysssislog 
       WHERE event
       = 
       'OnPipelineRowsSent'
    ) as data

ran in 9:20

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

AND USE IT

select *from dbo.fnSplitString('Querying SQL Server','')

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

if anyone wants to get only one part of the seperatured text can use this

select * from fromSplitStringSep('Word1 wordr2 word3',' ')

CREATE function [dbo].[SplitStringSep] 
(
    @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
  )

I devoloped this,

declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';

while CHARINDEX(@splitter,@x) != 0
begin
    set @item = LEFT(@x,CHARINDEX(@splitter,@x))
    set @x    = RIGHT(@x,len(@x)-len(@item) )
     select @item as item, @x as x;
end

the only attention you should is dot '.' that end of the @x is always should be there.

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:////allinonescript.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:////allinonescript.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); 

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
upvote
  flag
ooo ramazan abi naber – Mustafa Ekici
upvote
  flag
I like this solution as an option to return a single substring as opposed to getting a parsed table that you then need to select from. Using a table result has its uses, but for what I needed this worked perfectly. – James H

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)');
upvote
  flag
This should be the correct answer! – Brendan Gooden
declare @strng varchar(max)='hello john smith'
select (
    substring(
        @strng,
        charindex(' ', @strng) + 1,
        (
          (charindex(' ', @strng, charindex(' ', @strng) + 1))
          - charindex(' ',@strng)
        )
    ))

building on @NothingsImpossible solution, or, rather, comment on the most voted answer (just below the accepted one), i found the following quick-and-dirty solution fulfill my own needs - it has a benefit of being solely within SQL domain.

given a string "first;second;third;fourth;fifth", say, I want to get the third token. this works only if we know how many tokens the string is going to have - in this case it's 5. so my way of action is to chop the last two tokens away (inner query), and then to chop the first two tokens away (outer query)

i know that this is ugly and covers the specific conditions i was in, but am posting it just in case somebody finds it useful. cheers

select 
    REVERSE(
        SUBSTRING(
            reverse_substring, 
            0, 
            CHARINDEX(';', reverse_substring)
        )
    ) 
from 
(
    select 
        msg,
        SUBSTRING(
            REVERSE(msg), 
            CHARINDEX(
                ';', 
                REVERSE(msg), 
                CHARINDEX(
                    ';',
                    REVERSE(msg)
                )+1
            )+1,
            1000
        ) reverse_substring
    from 
    (
        select 'first;second;third;fourth;fifth' msg
    ) a
) b

Starting with SQL Server 2016 we string_split

DECLARE @string varchar(100) = 'Richard, Mike, Mark'

SELECT value FROM string_split(@string, ',')
upvote
  flag
This is well and good, but it doesn't address the question of getting the nth result. – Johnie Karr

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.