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.
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')
Alternative of string_split Function on lower compatibility level
Here is how I have approached this in pre-string_split days, by converting the list to an XML string and then using SQL Server's XML support.
DECLARE @list varchar(255) = 'value1,value2,value3,value4,value5';
SELECT
x.f.value( '.', 'varchar(50)' ) AS [value]
FROM (
SELECT CAST ( '<v><i>' + REPLACE ( @list, ',', '</i><i>' ) + '</i></v>' AS xml ) AS x
) AS d
CROSS APPLY x.nodes( '//v/i' ) x( f );
RETURNS
+--------+
| value |
+--------+
| value1 |
| value2 |
| value3 |
| value4 |
| value5 |
+--------+
You could convert this into an inline table-valued-function:
CREATE OR ALTER FUNCTION dbo.my_string_split (
@list varchar(1000), @delim varchar(1) = ','
)
RETURNS TABLE
AS
RETURN (
SELECT
x.f.value( '.', 'varchar(50)' ) AS [value]
FROM (
SELECT CAST ( '<v><i>' + REPLACE ( @list, @delim, '</i><i>' ) + '</i></v>' AS xml ) AS x
) AS d
CROSS APPLY x.nodes( '//v/i' ) x( f )
)
GO
To call it:
SELECT * FROM dbo.my_string_split( 'value1,value2,value3,value4,value5', ',' );
T-SQL split string based on delimiter
May be this will help you.
SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX('/', myColumn)
WHEN 0
THEN LEN(myColumn)
ELSE CHARINDEX('/', myColumn) - 1
END) AS FirstName
,SUBSTRING(myColumn, CASE CHARINDEX('/', myColumn)
WHEN 0
THEN LEN(myColumn) + 1
ELSE CHARINDEX('/', myColumn) + 1
END, 1000) AS LastName
FROM MyTable
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;
T-SQL: Opposite to string concatenation - how to split string into multiple records
There are a wide varieties of solutions to this problem documented here, including this little gem:
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
TSQL split string with ' '
This should do the trick but it's only a quick hack (SQL Fiddle):
SET @FirstName = substring(@FirstLastName
, 1
, charindex(' ', @FirstLastName)-1)
SET @LastName = substring(@FirstLastName
, charindex(' ', @FirstLastName)
, 999)
-- if you want to calculate the actual @LastName length,
-- replace 999 with len(@FirstLastName)-charindex(' ', @FirstLastName)+1
For a more elegant and robust way see Split function equivalent in T-SQL?.
How to split a string in T-SQL?
Use a table valued function like this,
CREATE FUNCTION Splitfn(@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
and get your variable and use this function like this,
SELECT i.items FROM dbo.Splitfn(@a,'|') AS i
T-SQL function to split string with two delimiters as column separators into table
You can find a split()
function on the web. Then, you can do string logic:
select left(val, charindex(':', val)) as col1,
substring(val, charindex(':', val) + 1, len(val)) as col2
from dbo.split(@str, ';') s(val);
Related Topics
How to Obtain a Query Execution Plan in SQL Server
Using Group by on Multiple Columns
How to Import an SQL File Using the Command Line in MySQL
How to Use Case Statement in a Join Condition
Select Values That Meet Different Conditions on Different Rows
MySQL Get Row Position in Order By
How to List Table Foreign Keys
In VS Any Operator in Postgresql
Remove Duplicate Rows in MySQL
Postgresql Unnest() With Element Number
Oracle Sql: Update a Table With Data from Another Table
Difference Between Numeric, Float and Decimal in SQL Server
How to Do a Batch Insert in MySQL