I’m looking to split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...' (comma delimited) into a table or table variable.
我想分裂的1、2、3、4、5,6,7,8,9,10,11,12日,13日,14日,15日…(逗号分隔)为一个表或表变量。
Does anyone have a function that returns each one in a row?
每个人都有一个函数返回每一行吗?
15 个解决方案
#1
46
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.
在SQL Server 2008中,你可以用。net代码实现同样的效果。也许它会运行得更快,但显然这种方法更容易管理。
#2
43
Try this
试试这个
DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)
OR
或
DECLARE @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
;WITH cte AS
(
SELECT 0 a, 1 b
UNION ALL
SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
FROM CTE
WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter)
THEN b - a - LEN(@delimiter)
ELSE LEN(@str) - a + 1 END) value
FROM cte WHERE a > 0
Many more ways of doing the same is here How to split comma delimited string?
这里还有很多方法可以用逗号分隔字符串?
#3
17
You've tagged this SQL Server 2008 but future visitors to this question (using SQL Server 2016+) will likely want to know about STRING_SPLIT
.
您已经标记了这个SQL Server 2008,但是未来的访问者(使用SQL Server 2016+)可能会想知道STRING_SPLIT。
With this new builtin function you can now just use
有了这个新的builtin函数,您现在就可以使用了。
SELECT TRY_CAST(value AS INT)
FROM STRING_SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',')
Some restrictions of this function and some promising results of performance testing are in this blog post by Aaron Bertrand.
这一功能的一些限制和性能测试的一些有希望的结果在这篇博文中由Aaron Bertrand撰写。
#4
10
This is most like .NET, for those of you who are familiar with that function:
这很像。net,对于熟悉这个函数的人来说
CREATE FUNCTION dbo.[String.Split]
(
@Text VARCHAR(MAX),
@Delimiter VARCHAR(100),
@Index INT
)
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX));
DECLARE @R VARCHAR(MAX);
WITH CTE AS
(
SELECT 0 A, 1 B
UNION ALL
SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter))
FROM CTE
WHERE B > A
)
INSERT @A(V)
SELECT SUBSTRING(@Text,A,CASE WHEN B > LEN(@Delimiter) THEN B-A-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE
FROM CTE WHERE A >0
SELECT @R
= V
FROM @A
WHERE ID = @Index + 1
RETURN @R
END
SELECT dbo.[String.Split]('121,2,3,0',',',1) -- gives '2'
#5
7
here is the split function that u asked
这是u要求的分裂函数。
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('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',')
#6
4
DECLARE
@InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
, @delimiter varchar(10) = ','
DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)
Source of this response: http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited
此响应的源:http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited。
#7
2
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
A selection of different methods
选择不同的方法。
#8
2
I am tempted to squeeze in my favourite solution. The resulting table will consist of 2 columns: PosIdx for position of the found integer; and Value in integer.
我很想挤进我最喜欢的解决方案。结果表将由2列组成:PosIdx用于找到的整数位置;在整数和价值。
create function FnSplitToTableInt
(
@param nvarchar(4000)
)
returns table as
return
with Numbers(Number) as
(
select 1
union all
select Number + 1 from Numbers where Number < 4000
),
Found as
(
select
Number as PosIdx,
convert(int, ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' collate Latin1_General_BIN,
@param + N',', Number) - Number))))) as Value
from
Numbers
where
Number <= len(@param)
and substring(N',' + @param, Number, 1) = N',' collate Latin1_General_BIN
)
select
PosIdx,
case when isnumeric(Value) = 1
then convert(int, Value)
else convert(int, null) end as Value
from
Found
It works by using recursive CTE as the list of positions, from 1 to 100 by default. If you need to work with string longer than 100, simply call this function using 'option (maxrecursion 4000)' like the following:
它使用递归CTE作为位置列表,默认情况下从1到100。如果您需要使用大于100的字符串,只需使用“选项(maxrecursion 4000)”如下所示:
select * from FnSplitToTableInt
(
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0'
)
option (maxrecursion 4000)
#9
2
This is another version which really does not have any restrictions (e.g.: special chars when using xml approach, number of records in CTE approach) and it runs much faster based on a test on 10M+ records with source string average length of 4000. Hope this could help.
这是另一个没有任何限制的版本(例如:使用xml方法时的特殊字符,CTE方法中记录的数量),而且它的运行速度要快得多,基于1000万+记录的测试,源字符串平均长度为4000。希望这可以帮助。
Create function [dbo].[udf_split] (
@ListString nvarchar(max),
@Delimiter nvarchar(1000),
@IncludeEmpty bit)
Returns @ListTable TABLE (ID int, ListValue nvarchar(1000))
AS
BEGIN
Declare @CurrentPosition int, @NextPosition int, @Item nvarchar(max), @ID int, @L int
Select @ID = 1,
@L = len(replace(@Delimiter,' ','^')),
@ListString = @ListString + @Delimiter,
@CurrentPosition = 1
Select @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
While @NextPosition > 0 Begin
Set @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition)))
If @IncludeEmpty=1 or LEN(@Item)>0 Begin
Insert Into @ListTable (ID, ListValue) Values (@ID, @Item)
Set @ID = @ID+1
End
Set @CurrentPosition = @NextPosition+@L
Set @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
End
RETURN
END
#10
2
CREATE FUNCTION Split
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
id int identity(1,1), -- I use this column for numbering splitted parts
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(max)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
GO
usage
使用
Select * from dbo.Split(N'1,2,3,4,6',',')
#11
1
/* *Object: UserDefinedFunction [dbo].[Split] Script Date: 10/04/2013 18:18:38* */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(@List varchar(8000),@SplitOn Nvarchar(5))
RETURNS @RtnValue table
(Id int identity(1,1),Value nvarchar(100))
AS
BEGIN
Set @List = Replace(@List,'''','')
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))
Return
END
go
Select *
From [Clv].[Split] ('1,2,3,3,3,3,',',')
GO
#12
0
This blog came with a pretty good solution using XML in T-SQL.
这个博客在T-SQL中使用XML有了一个很好的解决方案。
This is the function I came up with based on that blog (change function name and result type cast per need):
这是我在这个博客上提出的功能(更改函数名称和结果类型转换需要):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitIntoBigints]
(@List varchar(MAX), @Splitter char)
RETURNS TABLE
AS
RETURN
(
WITH SplittedXML AS(
SELECT CAST('<v>' + REPLACE(@List, @Splitter, '</v><v>') + '</v>' AS XML) AS Splitted
)
SELECT x.v.value('.', 'bigint') AS Value
FROM SplittedXML
CROSS APPLY Splitted.nodes('//v') x(v)
)
GO
#13
0
CREATE Function [dbo].[CsvToInt] ( @Array varchar(4000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(4000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
#14
0
Using tally table here is one split string function(best possible approach) by Jeff Moden
这里使用的是Jeff Moden的一个拆分字符串函数(最好的方法)。
CREATE FUNCTION [dbo].[DelimitedSplit8K]
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover NVARCHAR(4000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Referred from Tally OH! An Improved SQL 8K “CSV Splitter” Function
被称为从理货哦!改进的SQL 8K“CSV拆分器”功能。
#15
-3
You write this function in sql server after that problem will be solved.
在这个问题解决之后,在sql server中编写这个函数。
http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html
http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html
#1
46
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.
在SQL Server 2008中,你可以用。net代码实现同样的效果。也许它会运行得更快,但显然这种方法更容易管理。
#2
43
Try this
试试这个
DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)
OR
或
DECLARE @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
;WITH cte AS
(
SELECT 0 a, 1 b
UNION ALL
SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
FROM CTE
WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter)
THEN b - a - LEN(@delimiter)
ELSE LEN(@str) - a + 1 END) value
FROM cte WHERE a > 0
Many more ways of doing the same is here How to split comma delimited string?
这里还有很多方法可以用逗号分隔字符串?
#3
17
You've tagged this SQL Server 2008 but future visitors to this question (using SQL Server 2016+) will likely want to know about STRING_SPLIT
.
您已经标记了这个SQL Server 2008,但是未来的访问者(使用SQL Server 2016+)可能会想知道STRING_SPLIT。
With this new builtin function you can now just use
有了这个新的builtin函数,您现在就可以使用了。
SELECT TRY_CAST(value AS INT)
FROM STRING_SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',')
Some restrictions of this function and some promising results of performance testing are in this blog post by Aaron Bertrand.
这一功能的一些限制和性能测试的一些有希望的结果在这篇博文中由Aaron Bertrand撰写。
#4
10
This is most like .NET, for those of you who are familiar with that function:
这很像。net,对于熟悉这个函数的人来说
CREATE FUNCTION dbo.[String.Split]
(
@Text VARCHAR(MAX),
@Delimiter VARCHAR(100),
@Index INT
)
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX));
DECLARE @R VARCHAR(MAX);
WITH CTE AS
(
SELECT 0 A, 1 B
UNION ALL
SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter))
FROM CTE
WHERE B > A
)
INSERT @A(V)
SELECT SUBSTRING(@Text,A,CASE WHEN B > LEN(@Delimiter) THEN B-A-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE
FROM CTE WHERE A >0
SELECT @R
= V
FROM @A
WHERE ID = @Index + 1
RETURN @R
END
SELECT dbo.[String.Split]('121,2,3,0',',',1) -- gives '2'
#5
7
here is the split function that u asked
这是u要求的分裂函数。
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('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',')
#6
4
DECLARE
@InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
, @delimiter varchar(10) = ','
DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)
Source of this response: http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited
此响应的源:http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited。
#7
2
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
A selection of different methods
选择不同的方法。
#8
2
I am tempted to squeeze in my favourite solution. The resulting table will consist of 2 columns: PosIdx for position of the found integer; and Value in integer.
我很想挤进我最喜欢的解决方案。结果表将由2列组成:PosIdx用于找到的整数位置;在整数和价值。
create function FnSplitToTableInt
(
@param nvarchar(4000)
)
returns table as
return
with Numbers(Number) as
(
select 1
union all
select Number + 1 from Numbers where Number < 4000
),
Found as
(
select
Number as PosIdx,
convert(int, ltrim(rtrim(convert(nvarchar(4000),
substring(@param, Number,
charindex(N',' collate Latin1_General_BIN,
@param + N',', Number) - Number))))) as Value
from
Numbers
where
Number <= len(@param)
and substring(N',' + @param, Number, 1) = N',' collate Latin1_General_BIN
)
select
PosIdx,
case when isnumeric(Value) = 1
then convert(int, Value)
else convert(int, null) end as Value
from
Found
It works by using recursive CTE as the list of positions, from 1 to 100 by default. If you need to work with string longer than 100, simply call this function using 'option (maxrecursion 4000)' like the following:
它使用递归CTE作为位置列表,默认情况下从1到100。如果您需要使用大于100的字符串,只需使用“选项(maxrecursion 4000)”如下所示:
select * from FnSplitToTableInt
(
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
'9, 8, 7, 6, 5, 4, 3, 2, 1, 0'
)
option (maxrecursion 4000)
#9
2
This is another version which really does not have any restrictions (e.g.: special chars when using xml approach, number of records in CTE approach) and it runs much faster based on a test on 10M+ records with source string average length of 4000. Hope this could help.
这是另一个没有任何限制的版本(例如:使用xml方法时的特殊字符,CTE方法中记录的数量),而且它的运行速度要快得多,基于1000万+记录的测试,源字符串平均长度为4000。希望这可以帮助。
Create function [dbo].[udf_split] (
@ListString nvarchar(max),
@Delimiter nvarchar(1000),
@IncludeEmpty bit)
Returns @ListTable TABLE (ID int, ListValue nvarchar(1000))
AS
BEGIN
Declare @CurrentPosition int, @NextPosition int, @Item nvarchar(max), @ID int, @L int
Select @ID = 1,
@L = len(replace(@Delimiter,' ','^')),
@ListString = @ListString + @Delimiter,
@CurrentPosition = 1
Select @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
While @NextPosition > 0 Begin
Set @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition)))
If @IncludeEmpty=1 or LEN(@Item)>0 Begin
Insert Into @ListTable (ID, ListValue) Values (@ID, @Item)
Set @ID = @ID+1
End
Set @CurrentPosition = @NextPosition+@L
Set @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
End
RETURN
END
#10
2
CREATE FUNCTION Split
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
id int identity(1,1), -- I use this column for numbering splitted parts
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
select
r.value('.','varchar(max)') as item
from @xml.nodes('//root/r') as records(r)
RETURN
END
GO
usage
使用
Select * from dbo.Split(N'1,2,3,4,6',',')
#11
1
/* *Object: UserDefinedFunction [dbo].[Split] Script Date: 10/04/2013 18:18:38* */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(@List varchar(8000),@SplitOn Nvarchar(5))
RETURNS @RtnValue table
(Id int identity(1,1),Value nvarchar(100))
AS
BEGIN
Set @List = Replace(@List,'''','')
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))
Return
END
go
Select *
From [Clv].[Split] ('1,2,3,3,3,3,',',')
GO
#12
0
This blog came with a pretty good solution using XML in T-SQL.
这个博客在T-SQL中使用XML有了一个很好的解决方案。
This is the function I came up with based on that blog (change function name and result type cast per need):
这是我在这个博客上提出的功能(更改函数名称和结果类型转换需要):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SplitIntoBigints]
(@List varchar(MAX), @Splitter char)
RETURNS TABLE
AS
RETURN
(
WITH SplittedXML AS(
SELECT CAST('<v>' + REPLACE(@List, @Splitter, '</v><v>') + '</v>' AS XML) AS Splitted
)
SELECT x.v.value('.', 'bigint') AS Value
FROM SplittedXML
CROSS APPLY Splitted.nodes('//v') x(v)
)
GO
#13
0
CREATE Function [dbo].[CsvToInt] ( @Array varchar(4000))
returns @IntTable table
(IntValue int)
AS
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(4000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
Insert @IntTable
Values (Cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
#14
0
Using tally table here is one split string function(best possible approach) by Jeff Moden
这里使用的是Jeff Moden的一个拆分字符串函数(最好的方法)。
CREATE FUNCTION [dbo].[DelimitedSplit8K]
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover NVARCHAR(4000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Referred from Tally OH! An Improved SQL 8K “CSV Splitter” Function
被称为从理货哦!改进的SQL 8K“CSV拆分器”功能。
#15
-3
You write this function in sql server after that problem will be solved.
在这个问题解决之后,在sql server中编写这个函数。
http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html
http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html