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"?
This question is related to
sql
sql-server
tsql
split
The answer is
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
);
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
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
| A_L | N |
|--------|---|
| Hello | 0 |
| John | 1 |
| Smith | 2 |
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.
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:
With enough qualitative feedback, they may actually consider making some of these enhancements:
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():
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
If you check the following SQL tutorial on splitting string using SQL, you will find a number of functions that can be used to split a given string on SQL Server
For example, SplitAndReturnNth UDF function can be used to split a text using a separator and return the Nth piece as the output of the function
select dbo.SplitAndReturnNth('Hello John Smith',' ',2)
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
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
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
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);
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.
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
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
Using SQL Server 2016 and above. Use this code to TRIM strings, ignore NULL values and apply a row index in the correct order. It also works with a space delimiter:
DECLARE @STRING_VALUE NVARCHAR(MAX) = 'one, two,,three, four, five'
SELECT ROW_NUMBER() OVER (ORDER BY R.[index]) [index], R.[value] FROM
(
SELECT
1 [index], NULLIF(TRIM([value]), '') [value] FROM STRING_SPLIT(@STRING_VALUE, ',') T
WHERE
NULLIF(TRIM([value]), '') IS NOT NULL
) R
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
Starting with SQL Server 2016 we string_split
DECLARE @string varchar(100) = 'Richard, Mike, Mark'
SELECT value FROM string_split(@string, ',')
declare @strng varchar(max)='hello john smith'
select (
substring(
@strng,
charindex(' ', @strng) + 1,
(
(charindex(' ', @strng, charindex(' ', @strng) + 1))
- charindex(' ',@strng)
)
))
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
)
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';
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
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')
)
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
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',' ')
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')
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.
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
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
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','**')
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
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
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
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','')
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.
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.
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
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
)
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()));
}
};
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.
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
I realize this is a really old question, but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP's question--and without splitting strings or resorting to a user-defined function. To access an item at a particular index of a delimited string, use the JSON_VALUE function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes " and the delimiter must be a comma ,, with the entire string enclosed in square brackets [].
DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
SELECT
JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
JSON_VALUE(@SampleString, '$[2]') AS Element3Value;
Output
Element1Value Element2Value Element3Value
--------------------- ------------------- ------------------------------
Hello John Smith
(1 row affected)
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?
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.
Source: Stackoverflow.com