Split function equivalent in T-SQL?
Here is somewhat old-fashioned solution:
/*
Splits string into parts delimitered with specified character.
*/
CREATE FUNCTION [dbo].[SDF_SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
@iPos int
if substring( @sString, 1, 1 ) = @cDelimiter
begin
set @iStart = 2
insert into @tParts
values( null )
end
else
set @iStart = 1
while 1=1
begin
set @iPos = charindex( @cDelimiter, @sString, @iStart )
if @iPos = 0
set @iPos = len( @sString )+1
if @iPos - @iStart > 0
insert into @tParts
values ( substring( @sString, @iStart, @iPos-@iStart ))
else
insert into @tParts
values( null )
set @iStart = @iPos+1
if @iStart > len( @sString )
break
end
RETURN
END
In SQL Server 2008 you can achieve the same with .NET code. Maybe it would work faster, but definitely this approach is easier to manage.
Split function by comma in SQL Server 2008
I've change the function name so it won't overlapped in what the Split()
function really does.
Here is the code:
CREATE FUNCTION dbo.GetColumnValue(
@String varchar(8000),
@Delimiter char(1),
@Column int = 1
)
returns varchar(8000)
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return null
declare @ColCnt int
set @ColCnt = 1
while (@idx != 0)
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0 begin
if (@ColCnt = @Column) return left(@String,@idx - 1)
set @ColCnt = @ColCnt + 1
end
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return @String
end
And here is the usage:
select dbo.GetColumnValue('Col1,Field2,VAlue3', ',', 3)
T-SQL split string
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')
How to split a comma-separated value to columns
CREATE FUNCTION [dbo].[fn_split_string_to_column] (
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @out_put TABLE (
[column_id] INT IDENTITY(1, 1) NOT NULL,
[value] NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @value NVARCHAR(MAX),
@pos INT = 0,
@len INT = 0
SET @string = CASE
WHEN RIGHT(@string, 1) != @delimiter
THEN @string + @delimiter
ELSE @string
END
WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
BEGIN
SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
SET @value = SUBSTRING(@string, @pos, @len)
INSERT INTO @out_put ([value])
SELECT LTRIM(RTRIM(@value)) AS [column]
SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
END
RETURN
END
How do I split a delimited string so I can access individual items?
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
STRING_SPLIT in SQL Server 2012
Other approach is to use XML
Method with CROSS APPLY
to split your Comma Separated Data :
SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);
Result :
DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Example :
DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id NVARCHAR(300),
marks NVARCHAR(300)
);
--insert into @StudentsMark
;WITH CTE
AS (
SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
ROW_NUMBER() OVER(ORDER BY
(
SELECT NULL
)) RN
FROM
(
SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)),
CTE1
AS (
SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
ROW_NUMBER() OVER(ORDER BY
(
SELECT NULL
)) RN
FROM
(
SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a))
INSERT INTO @StudentsMark
SELECT C.id,
C1.marks
FROM CTE C
LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;
Related Topics
Like Operation Returns No Rows on Nvarchar Column Filter If the Column Data Start with Numeric
Ssis - Performing a Lookup on Another Table to Get Related Column
How to Call a User Defined Function to Use with Select, Group By, Order By
SQL Query That Distinguishes Between ß and Ss
What's the Asymptotic Complexity of Groupby Operation
How to Create a Calculated Column in a SQL Server 2008 Table
Bigquery - Joining on Multiple Conditions Using Subqueries and or Statements
Performance and Readability of Regexp_Substr VS Instr and Substr
SQL Server - Dirty Reads Pros & Cons
Simple Update Statement So That All Rows Are Assigned a Different Value
How to Update with Inner Join in Oracle
How to Write Blob from Oracle Column to the File System
Insert into Values with Where Clause
Pivot Table with Non-Cardinal Values