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

Question

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

• Jarrod Dixon: See stackoverflow.com/questions/314824/… as well
• Gottfried Lesigang: 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.
• Gorgi Rankovski: I have added a new answer with more up-to-date approach: stackoverflow.com/a/49669994/632604
• José Dız: Try Get the nth element of a list -> portosql.wordpress.com/2019/05/27/enesimo-elemento-lista
• Greg: @TimAbell, the documentation states that "The order is not guaranteed to match the order of the substrings in the input string".

Answer 1

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?

• Nathan Bedford: 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.
• Factor Mystic: This is a great hack, and also makes me weep that something like this is necessary for something so friggin simple in real languages.
• NothingsImpossible: 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
• Bacon Bits: @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.
• Tim Abell: @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.

Answer 2

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

• Beth: 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)?
• Filip De Vos: @GateKiller This solution does not support Unicode & it uses hard coded numeric(18,3) which doesn't make it a viable "reusable" function.
• jjxtra: This works but allocates a lot of memory and wastes CPU.
• qJake: 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.
• Brandon Griffin: 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.

Answer 3

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.

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

Answer 4

Most of the solutions here use while loops or recursive CTEs. A set-based approach will be superior, I promise, if you can use a delimiter other than a space:

CREATE FUNCTION [dbo].[SplitString]
 (
 @List NVARCHAR(MAX),
 @Delim VARCHAR(255)
 )
 RETURNS TABLE
 AS
 RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM 
 ( 
 SELECT n = Number, 
 [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
 );

Sample usage:

SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
 WHERE idx = 3;

Results:

----
blat

You could also add the idx you want as an argument to the function, but I'll leave that as an exercise to the reader.

You can't do this with just the native STRING_SPLIT function added in SQL Server 2016, because there is no guarantee that the output will be rendered in the order of the original list. In other words, if you pass in 3,6,1 the result will likely be in that order, but it could be 1,3,6. I have asked for the community's help in improving the built-in function here:

• Please help with STRING_SPLIT improvements

With enough qualitative feedback, they may actually consider making some of these enhancements:

• STRING_SPLIT is not feature complete

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:

• Split strings the right way – or the next best way
• Splitting Strings : A Follow-Up
• Splitting Strings : Now with less T-SQL
• Comparing string splitting / concatenation methods
• Processing a list of integers : my approach
• Splitting a list of integers : another roundup
• More on splitting lists : custom delimiters, preventing duplicates, and maintaining order
• Removing Duplicates from Strings in SQL Server

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

• Performance Surprises and Assumptions : STRING_SPLIT()
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #1
• STRING_SPLIT() in SQL Server 2016 : Follow-Up #2
• SQL Server v.Next : STRING_AGG() performance
• Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions

• T-moty: 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?
• wwmbes: 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: @AaronBertrand The original problem posted by GateKiller involves a space delimiter.
• Aaron Bertrand: @user1255933 Addressed.
• Aaron Bertrand: @Michael Yes, that’s true. You also wouldn’t have a table to select from if you didn’t have ALTER SCHEMA permission, and wouldn’t be able to select from it if you don’t have SELECT permission You could always ask someone to create the function for you. Or create it somewhere you can create it (even temporarily, say in tempdb). And on 2016+ you should be using STRING_SPLIT() and not a function you have to create yourself anyway.

Answer 5

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...

UPDATE 2 - Get the values type-safe

We can use an array within an array simply by using doubled [[]]. This allows for a typed WITH-clause:

DECLARE @SomeDelimitedString VARCHAR(100)='part1|1|20190920';
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');
SELECT @SomeDelimitedString AS TheOriginal
 ,@JsonArray AS TransformedToJSON
 ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
 ,TheSecondFragment INT '$[1]'
 ,TheThirdFragment DATE '$[2]') ValuesFromTheArray

• Salman Arshad: Re: if your string might include forbidden characters... you could simply wrap the substrings like so <x><![CDATA[x<&>x]]></x>.
• Gottfried Lesigang: @SalmanA, yeah ,CDATA-sections can deal with this too... But after the cast they are gone (changed to escaped text() implicitly). I do not like magic under the hood, so I'd prefer the (SELECT 'Text with <&>' AS [*] FOR XML PATH('')) - approach. This looks cleaner to me and happens anyway... (Some more about CDATA and XML).
• Martin Smith: Before doing the REPLACE in the JSON version you should call STRING_ESCAPE(@SomeDelimitedString, 'JSON') dbfiddle.uk/mWShPwRP

Answer 6

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.

• Pking: The best solution IMO, the others have some kind of limitation.. this is fast and can parse long strings with many elements.
• hatchet - done with SOverflow: 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?
• nathan_jr: Agreed, would be more intuitive in the asc direction. I was following parsename() convention which uses desc
• Tim Abell: some explanation as to how this works would be great
• wwmbes: 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?

Answer 7

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','**')

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

Answer 8

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',' ')

• Alan: I liked this solution. Expanded it to return a scalar value based on the specified column within the results.
• KeithL: I got burned with an '&' in the string to be split using this

Answer 9

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()));
 }
};

• Guillermo Gutiérrez: 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.
• Solomon Rutzky: @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.

Answer 10

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

• angel: i used your answer but did not work, but i modified and this worked with union all, i am using sql 2005

Answer 11

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.

• Andrew Hill: 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)

Answer 12

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

• Abdurrahman I.: This is really great i've ever seen in sql stuff, it worked for my job and i appreciate that, thanks !
• Kristen Hammack: 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.
• MattE: This Works perfectly!! i was looking into using a function(SplitStrings_Moden) from here: sqlperformance.com/2012/07/t-sql-queries/split-strings#comments that does this and it was taking a minute and a half to split the data and return the rows when only using 4 account numbers. I tested your version with a left join on the table with the data on account numbers and it took like 2 or 3 seconds! Huge difference and works flawlessly! I'd give this 20 votes if possible!
• Lomak: Vulnerable to sql injection

Answer 13

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

• James H: 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.

Answer 14

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';

• Gorgi Rankovski: The trick is in the OFFSET 1 ROWS, which will skip the first item and will return the second item. If your indexes are 0-based and @X is the variable holding the item index you want to fetch, you can sure do OFFSET @X ROWS
• Gottfried Lesigang: Okay, did not use this before... Nice to know... I'd still prefer the xml-split based approach, as it allows to fetch the value type-safe and does not need a sub-query, but this is a good one. +1 from my side
• user1443098: the problem here is that STRING_SPLIT does not guarantee the order of the returned results. So your item 1 may or may not be my item 1.
• Gottfried Lesigang: @GorgiRankovski, Using STRING_SPLIT demands for v2016+. In this case it is much better to use OPENJSON or JSON_VALUE. You might want to check my answer

Answer 15

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')

• Vikas: 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!

Answer 16

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

• Gaslight Deceive Subvert: You can't easily access the Nth item using this function.

Answer 17

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

Answer 18

SQL Server 2022 supports the following signature of STRING_SPLIT:

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

When enable_ordinal flag is set to 1 the result will include a column named ordinal that consists of the 1‐based position of the substring within the input string:

SELECT *
FROM STRING_SPLIT('hello john smith', ' ', 1)
| value | ordinal |
|-------|---------|
| hello | 1 |
| john | 2 |
| smith | 3 |

This allows us to do this:

SELECT value
FROM STRING_SPLIT('hello john smith', ' ', 1)
WHERE ordinal = 2
| value |
|-------|
| john |

Or this:

SELECT str, substr
FROM (VALUES
 ('hello john smith'),
 ('hello jane'),
 ('hello')
) AS t(str)
OUTER APPLY (
 SELECT value
 FROM STRING_SPLIT(str, ' ', 1)
 WHERE ordinal = 2
) AS a(substr)
| str | substr |
|------------------|--------|
| hello john smith | john |
| hello jane | jane |
| hello | null |

Answer 19

 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

Answer 20

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); 

Answer 21

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

• Gottfried Lesigang: This is to complicated... No need for .nodes(). You can place the XQuery into .value() directly (see my answer). Btw: Scalar funcitons are very bad performers. Much better was an inline TVF, even if it returns just one cell in one row...
• adudley: This is slow, but it does actually work, thanks. [unlike the junk ChatGPT spat out.. maybe stackoverflow needs a new name, for when ChatGPT can't, Stackoverflow can]

Answer 22

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

Answer 23

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

Answer 24

Almost all the other answers 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.

Answer 25

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 |

Answer 26

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

Answer 27

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','')

Answer 28

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
 )

Answer 29

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.

Answer 30

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

• Gottfried Lesigang: this works only if we know how many tokens the string is going to have - a breaking limitation...