T-SQL split string

I have a SQL Server 2008 R2 column containing a string which I need to split by a comma. I have seen many answers on StackOverflow but none of them works in R2. I have made sure I have select permissions on any split function examples. Any help greatly appreciated.

I've used this SQL before which may work for you:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END

and to use it:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

if you replace

WHILE CHARINDEX(',', @stringToSplit) > 0

with

WHILE LEN(@stringToSplit) > 0

you can eliminate that last insert after the while loop!

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE LEN(@stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)


if @pos = 0
SELECT @pos = LEN(@stringToSplit)


SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

RETURN
END

I had to write something like this recently. Here's the solution I came up with. It's generalized for any delimiter string and I think it would perform slightly better:

CREATE FUNCTION [dbo].[SplitString]
( @string nvarchar(4000)
, @delim nvarchar(100) )
RETURNS
@result TABLE
( [Value] nvarchar(4000) NOT NULL
, [Index] int NOT NULL )
AS
BEGIN
DECLARE @str nvarchar(4000)
, @pos int
, @prv int = 1

SELECT @pos = CHARINDEX(@delim, @string)
WHILE @pos > 0
BEGIN
SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
INSERT INTO @result SELECT @str, @prv

SELECT @prv = @pos + LEN(@delim)
, @pos = CHARINDEX(@delim, @string, @pos + 1)
END

INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
RETURN
END

A solution using a CTE, if anyone should need that (apart from me, who obviously did, that is why I wrote it).

declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';

with StringToSplit as (
select
ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
, substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail

union all

select
ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
, substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
from StringToSplit
where charindex( @SplitChar, Tail ) > 0

union all

select
ltrim( rtrim( Tail ) ) Head
, '' Tail
from StringToSplit
where charindex( @SplitChar, Tail ) = 0
and len( Tail ) > 0
)
select Head from StringToSplit

This is more narrowly-tailored. When I do this I usually have a comma-delimited list of unique ids (INT or BIGINT), which I want to cast as a table to use as an inner join to another table that has a primary key of INT or BIGINT. I want an in-line table-valued function returned so that I have the most efficient join possible.

Sample usage would be:

DECLARE @IDs VARCHAR(1000);
SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,';
SELECT me.Value
FROM dbo.MyEnum me
INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID

I stole the idea from

[To see links please register here]

, changing it to be in-line table-valued and cast as INT.

create function dbo.GetIntIDTableFromDelimitedString
(
@IDs VARCHAR(1000) --this parameter must start and end with a comma, eg ',123,456,'
--all items in list must be perfectly formatted or function will error
)
RETURNS TABLE AS
RETURN

SELECT
CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID
FROM
[master].[dbo].[spt_values] Nums
WHERE Nums.Type = 'P'
AND Nums.number BETWEEN 1 AND DATALENGTH(@IDs)
AND SUBSTRING(@IDs,Nums.number,1) = ','
AND CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number;

GO

here is a version that can split on a pattern using patindex, a simple adaptation of the post above. I had a case where I needed to split a string that contained multiple separator chars.
<pre><code>
alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
BEGIN
SELECT @pos = PATINDEX(@splitPattern, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');
</code></pre>
result looks like this

stringa
stringb
x
y
z

ALTER FUNCTION [dbo].func_split_string
(
@input as varchar(max),
@delimiter as varchar(10) = ";"

)
RETURNS @result TABLE
(
id smallint identity(1,1),
csv_value varchar(max) not null
)
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @string AS VARCHAR(MAX) = '';

WHILE LEN(@input) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimiter,@input);

IF(@pos<=0)
select @pos = len(@input)

IF(@pos <> LEN(@input))
SELECT @string = SUBSTRING(@input, 1, @pos-1);
ELSE
SELECT @string = SUBSTRING(@input, 1, @pos);

INSERT INTO @result SELECT @string

SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)
END
RETURN
END

I needed a quick way to get rid of the `+4` from a **zip code**.

UPDATE #Emails
SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1))
WHERE ZIPCode LIKE '%-%'

No proc... no UDF... just one tight little inline command that does what it must. Not fancy, not elegant.

Change the delimiter as needed, etc, and it will work for anything.

Personnaly I use this function :

ALTER FUNCTION [dbo].[CUST_SplitString]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)