+- 0Day Forums (https://zeroday.vip)
+-- Forum: Coding (https://zeroday.vip/Forum-Coding)
+--- Forum: Database (https://zeroday.vip/Forum-Database)
+---- Forum: Microsoft SQL Server (https://zeroday.vip/Forum-Microsoft-SQL-Server)
+---- Thread: T-SQL split string (/Thread-T-SQL-split-string)
T-SQL split string - deviators128792 - 07-31-2023
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.
RE: T-SQL split string - gazella809131 - 07-31-2023
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')
RE: T-SQL split string - bedub370175 - 07-31-2023
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
RE: T-SQL split string - berried950587 - 07-31-2023
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
RE: T-SQL split string - sixtus60823 - 07-31-2023
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
RE: T-SQL split string - Mrincrustation248 - 07-31-2023
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
RE: T-SQL split string - aureaydqczgqw - 07-31-2023
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
RE: T-SQL split string - immunochemistry725 - 07-31-2023
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
RE: T-SQL split string - samaj549727 - 07-31-2023
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.
RE: T-SQL split string - adjunctively990108 - 07-31-2023
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
)