blocks|key|1593851|text|下面是一些老式的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1593852|/*
++++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+)%2B1
++++++++if+@iPos+-+@iStart+>+0++++++++++
++++++++++++insert+into+@tParts
++++++++++++values++(+substring(+@sString,+@iStart,+@iPos-@iStart+))
++++++++else
++++++++++++insert+into+@tParts
++++++++++++values(+null+)
++++++++set+@iStart+=+@iPos%2B1
++++++++if+@iStart+>+len(+@sString+)+
++++++++++++break
++++end
++++RETURN

END|code-block|syntax|javascript|1593853|在SQL+Server2008中，您可以使用.NET代码实现相同的功能。也许它会工作得更快，但这种方法肯定更容易管理。|1593854|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is somewhat old-fashioned solution:

<pre><code>/*
 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 &gt; 0 
 insert into @tParts
 values ( substring( @sString, @iStart, @iPos-@iStart ))
 else
 insert into @tParts
 values( null )
 set @iStart = @iPos+1
 if @iStart &gt; len( @sString ) 
 break
 end
 RETURN

END
</code></pre>

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.

blocks|key|1593920|text|我很想挤进我最喜欢的解决方案。结果表将由2列组成:+PosIdx表示找到的整数的位置；Value表示整数。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1593921|create+function+FnSplitToTableInt
(
++++@param+nvarchar(4000)
)
returns+table+as
return
++++with+Numbers(Number)+as+
++++(
++++++++select+1+
++++++++union+all+
++++++++select+Number+%2B+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+%2B+N',',+Number)+-+Number)))))+as+Value
++++++++from+++
++++++++++++Numbers+
++++++++where++
++++++++++++Number+<=+len(@param)
++++++++and+substring(N','+%2B+@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|code-block|syntax|javascript|1593922|它的工作方式是使用递归CTE作为位置列表，默认情况下从1到100。如果您需要处理长度超过100的字符串，只需使用如下所示的'option+(maxrecursion+4000)‘调用此函数：|1593923|select+*+from+FnSplitToTableInt
(
++++'9,+8,+7,+6,+5,+4,+3,+2,+1,+0,+'+%2B+
++++'9,+8,+7,+6,+5,+4,+3,+2,+1,+0,+'+%2B
++++'9,+8,+7,+6,+5,+4,+3,+2,+1,+0,+'+%2B
++++'9,+8,+7,+6,+5,+4,+3,+2,+1,+0,+'+%2B
++++'9,+8,+7,+6,+5,+4,+3,+2,+1,+0'
)+
option+(maxrecursion+4000)|1593924|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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.

<pre><code>
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 &lt; 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 &lt;= 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
</code></pre>

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:

<pre><code>
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)
</code></pre>

blocks|key|1378794|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1378795|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>'%2Breplace(@str,+@delimiter,+'</X><X>')%2B'</X>')+as+xml)
SELECT+C.value('.',+'varchar(10)')+as+value+FROM+@xml.nodes('X')+as+X(C)|code-block|syntax|javascript|1378796|或|1378797|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)+%2B+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+%2B+1+END)+value++++++
FROM+cte+WHERE+a+>+0|1378798|这里提供了更多实现相同功能的方法How+to+split+comma+delimited+string?|offset|length|1378799|entityMap|0|LINK|mutability|MUTABLE|url|http://social.msdn.microsoft.com/Forums/en-IE/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85^0|0|0|0|0|G|10|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Try this

<pre><code>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(('&lt;X&gt;'+replace(@str, @delimiter, '&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)
</code></pre>

OR

<pre><code>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 &gt; a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b &gt; LEN(@delimiter) 
 THEN b - a - LEN(@delimiter) 
 ELSE LEN(@str) - a + 1 END) value 
FROM cte WHERE a &gt; 0
</code></pre>

Many more ways of doing the same is here <a href="http://social.msdn.microsoft.com/Forums/en-IE/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85" rel="noreferrer">How to split comma delimited string?</a>

blocks|key|1594065|text|对于熟悉该函数的人来说，这非常类似于.NET：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1594066|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)+%2B+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+%2B+1+END)+VALUE++++++
++++FROM+CTE+WHERE+A+>0

++++SELECT++++++@R
++++=+++++++++++V
++++FROM++++++++@A
++++WHERE+++++++ID+=+@Index+%2B+1
++++RETURN++++++@R
END

SELECT+dbo.[String.Split]('121,2,3,0',',',1)+--+gives+'2'|code-block|syntax|javascript|1594067|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This is most like .NET, for those of you who are familiar with that function:

<pre><code>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 &gt; A
 )
 INSERT @A(V)
 SELECT SUBSTRING(@Text,A,CASE WHEN B &gt; LEN(@Delimiter) THEN B-A-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE 
 FROM CTE WHERE A &gt;0

 SELECT @R
 = V
 FROM @A
 WHERE ID = @Index + 1
 RETURN @R
END

SELECT dbo.[String.Split]('121,2,3,0',',',1) -- gives '2'
</code></pre>

blocks|key|1378932|text|This+blog提供了一个在T-SQL中使用XML的非常好的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1378933|这是我基于该博客提出的函数(根据需要更改函数名称和结果类型)：|1378934|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>'+%2B+REPLACE(@List,+@Splitter,+'</v><v>')+%2B+'</v>'+AS+XML)+AS+Splitted
++++)
++++SELECT+x.v.value('.',+'bigint')+AS+Value
++++FROM+SplittedXML
++++CROSS+APPLY+Splitted.nodes('//v')+x(v)
)
GO|code-block|syntax|javascript|1378935|entityMap|0|LINK|mutability|MUTABLE|url|http://beyondrelational.com/modules/2/blogs/28/posts/10300/xquery-lab-19-how-to-parse-a-delimited-string.aspx^0|0|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

<a href="http://beyondrelational.com/modules/2/blogs/28/posts/10300/xquery-lab-19-how-to-parse-a-delimited-string.aspx" rel="nofollow">This blog</a> came with a pretty good solution using XML in T-SQL.

This is the function I came up with based on that blog (change function name and result type cast per need):

<pre><code>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('&lt;v&gt;' + REPLACE(@List, @Splitter, '&lt;/v&gt;&lt;v&gt;') + '&lt;/v&gt;' AS XML) AS Splitted
 )
 SELECT x.v.value('.', 'bigint') AS Value
 FROM SplittedXML
 CROSS APPLY Splitted.nodes('//v') x(v)
)
GO
</code></pre>

blocks|key|1594166|text|下面是您要求的拆分函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1594167|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>'+%2B+REPLACE(@delimited,@delimiter,'</t><t>')+%2B+'</t>'

++++++++++INSERT+INTO+@t(val)
++++++++++SELECT++r.value('.','varchar(MAX)')+as+item
++++++++++FROM++@xml.nodes('/t')+as+records(r)
++++++++++RETURN
++++++++END|code-block|syntax|javascript|1594168|像这样执行函数|1594169|select+*+from+dbo.split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',')|1594170|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

here is the split function that u asked

<pre><code>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'&lt;t&gt;' + REPLACE(@delimited,@delimiter,'&lt;/t&gt;&lt;t&gt;') + '&lt;/t&gt;'

 INSERT INTO @t(val)
 SELECT r.value('.','varchar(MAX)') as item
 FROM @xml.nodes('/t') as records(r)
 RETURN
 END
</code></pre>

execute the function like this

<pre><code>select * from dbo.split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',')
</code></pre>

blocks|key|1594178|text|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+%2B+','

while+patindex('%25,%25'+,+@array)+<>+0+
begin

select+@separator_position+=+patindex('%25,%25'+,+@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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1594179|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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) &lt;&gt; 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
</code></pre>

blocks|key|1594234|text|这是另一个版本，它实际上没有任何限制(例如:使用xml方法时的特殊字符，CTE方法中的记录数)，并且基于对源串平均长度为4000的10M%2B记录的测试，它的运行速度要快得多。希望这能有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1594235|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,'+','%5E')),
++++++++++++@ListString+=+@ListString+%2B+@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%2B1
+++End
+++Set++@CurrentPosition+=+@NextPosition%2B@L
+++Set++@NextPosition+=+Charindex(@Delimiter,+@ListString,+@CurrentPosition)
++End
++++RETURN
END|code-block|syntax|javascript|1594236|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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. 

<pre><code>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 &gt; 0 Begin
 Set @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition)))
 If @IncludeEmpty=1 or LEN(@Item)&gt;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
</code></pre>

blocks|key|1379065|text|/*+*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)%2Blen(@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|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1379066|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>/* *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)&gt;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
</code></pre>

blocks|key|1379093|text|DECLARE
++++@InputString+NVARCHAR(MAX)+=+'token1,token2,token3,token4,token5'
++++,+@delimiter+varchar(10)+=+','

DECLARE+@xml+AS+XML+=+CAST(('<X>'%2BREPLACE(@InputString,@delimiter+,'</X><X>')%2B'</X>')+AS+XML)
SELECT+C.value('.',+'varchar(10)')+AS+value
FROM+@xml.nodes('X')+as+X(C)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1379094|此响应的来源：http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited|unstyled|offset|length|1379095|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited^0|0|7|1Y|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@$G|S|H|T|1|U]]|A|$]]|$1|I|3|-4|5|F|7|V|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P]]]]

<pre><code>DECLARE
 @InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
 , @delimiter varchar(10) = ','

DECLARE @xml AS XML = CAST(('&lt;X&gt;'+REPLACE(@InputString,@delimiter ,'&lt;/X&gt;&lt;X&gt;')+'&lt;/X&gt;') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)
</code></pre>

Source of this response:
<a href="http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited" rel="nofollow">http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited</a>

blocks|key|1594308|text|在sql+server中编写此函数后，该问题将得到解决。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1594309|http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html|offset|length|1594310|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2D|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

You write this function in sql server after that problem will be solved.

<a href="http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html" rel="nofollow">http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html</a>

blocks|key|1379169|text|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>'+%2B+replace(@delimited,@delimiter,'</r><r>')+%2B+'</r></root>'

++insert+into+@t(val)
++select
++++r.value('.','varchar(max)')+as+item
++from+@xml.nodes('//root/r')+as+records(r)

++RETURN
END
GO|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1379170|用法|unstyled|1379171|Select+*+from+dbo.Split(N'1,2,3,4,6',',')|1379172|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>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'&lt;root&gt;&lt;r&gt;' + replace(@delimited,@delimiter,'&lt;/r&gt;&lt;r&gt;') + '&lt;/r&gt;&lt;/root&gt;'

 insert into @t(val)
 select
 r.value('.','varchar(max)') as item
 from @xml.nodes('//root/r') as records(r)

 RETURN
END
GO
</code></pre>

usage

<pre><code>Select * from dbo.Split(N'1,2,3,4,6',',')
</code></pre>

blocks|key|1594375|text|您已经为此SQL+Server2008添加了标签，但此问题的未来访问者(使用SQL+Server+2016%2B)可能希望了解STRING_SPLIT。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1594376|有了这个新的内置函数，您现在只需使用|1594377|SELECT+TRY_CAST(value+AS+INT)
FROM+++STRING_SPLIT+('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',+',')+|code-block|syntax|javascript|1594378|该函数的一些限制和性能测试的一些有希望的结果在this+blog+post+by+Aaron+Bertrand中。|1594379|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-gb/library/mt684588.aspx|1|http://sqlperformance.com/2016/03/t-sql-queries/string-split^0|1P|C|1P|C|0|0|0|0|N|W|1|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@$9|11|A|12|1|13]]|E|$]]|$1|F|3|G|5|6|7|14|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|15|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|16|8|@]|D|@$9|17|A|18|1|19]]|E|$]]|$1|O|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]]]

You've tagged this SQL Server 2008 but future visitors to this question (using SQL Server 2016+) will likely want to know about <a href="https://msdn.microsoft.com/en-gb/library/mt684588.aspx"><code>STRING_SPLIT</code></a>.

With this new builtin function you can now just use 

<pre><code>SELECT TRY_CAST(value AS INT)
FROM STRING_SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',') 
</code></pre>

Some restrictions of this function and some promising results of performance testing are in <a href="http://sqlperformance.com/2016/03/t-sql-queries/string-split">this blog post by Aaron Bertrand</a>.

blocks|key|1379322|text|这个简单的CTE将提供所需的内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1379323|DECLARE+@csv+varchar(max)+=+'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15';
--append+comma+to+the+list+for+CTE+to+work+correctly
SET+@csv+=+@csv+%2B+',';
--remove+double+commas+(empty+entries)
SET+@csv+=+replace(@csv,+',,',+',');
WITH+CteCsv+AS+(
++++SELECT+CHARINDEX(',',+@csv)+idx,+SUBSTRING(@csv,+1,+CHARINDEX(',',+@csv)+-+1)+[Value]
++++UNION+ALL
++++SELECT+CHARINDEX(',',+@csv,+idx+%2B+1),+SUBSTRING(@csv,+idx+%2B+1,+CHARINDEX(',',+@csv,+idx+%2B+1)+-+idx+-+1)+FROM+CteCsv
++++WHERE+CHARINDEX(',',+@csv,+idx+%2B+1)+>+0
)

SELECT+[Value]+FROM+CteCsv|code-block|syntax|javascript|1379324|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This simple CTE will give what's needed:

<pre><code>DECLARE @csv varchar(max) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15';
--append comma to the list for CTE to work correctly
SET @csv = @csv + ',';
--remove double commas (empty entries)
SET @csv = replace(@csv, ',,', ',');
WITH CteCsv AS (
 SELECT CHARINDEX(',', @csv) idx, SUBSTRING(@csv, 1, CHARINDEX(',', @csv) - 1) [Value]
 UNION ALL
 SELECT CHARINDEX(',', @csv, idx + 1), SUBSTRING(@csv, idx + 1, CHARINDEX(',', @csv, idx + 1) - idx - 1) FROM CteCsv
 WHERE CHARINDEX(',', @csv, idx + 1) &gt; 0
)

SELECT [Value] FROM CteCsv
</code></pre>

blocks|key|1594574|text|这对我很有用，https://www.sqlshack.com/the-string-split-function-in-sql-server/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1594575|在对这个主题进行了两个小时的研究之后，这是最简单的解决方案(不使用XML等)。|1594576|您应该只记得在from之后使用string_split。|1594577|DROP+TABLE+IF+EXISTS+#Countries
GO
DROP+TABLE+IF+EXISTS+#CityList
GO
CREATE+TABLE+#Countries
(Continent+VARCHAR(100),
Country+VARCHAR(100))
GO
CREATE+TABLE+#CityList
(Country+VARCHAR(100),
City+VARCHAR(5000))
GO
INSERT+INTO++#Countries
VALUES('Europe','France'),('Europe','Germany')+
+
INSERT+INTO+#CityList
VALUES('France','Paris,Marsilya,Lyon,Lille,Nice'),+('Germany','Berlin,Hamburg,Munih,Frankfurt,Koln')
+
SELECT+
CN.Continent,CN.Country,value
FROM+#CityList+CL+CROSS+APPLY+string_split(CL.City,',')++INNER+JOIN+
#Countries+CN+ON++CL.Country+=+CN.Country
+
+
DROP+TABLE+IF+EXISTS+#Countries
GO
DROP+TABLE+IF+EXISTS+#CityList|code-block|syntax|javascript|1594578|entityMap|0|LINK|mutability|MUTABLE|url|https://www.sqlshack.com/the-string-split-function-in-sql-server/^0|7|1T|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|C|$K|L]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

This works great for me <a href="https://www.sqlshack.com/the-string-split-function-in-sql-server/" rel="nofollow noreferrer">https://www.sqlshack.com/the-string-split-function-in-sql-server/</a>
After two hours of resarching this topic this is the simplest solution (without using XML ect.).
You should only remember to use string_split after from.
<pre><code>DROP TABLE IF EXISTS #Countries
GO
DROP TABLE IF EXISTS #CityList
GO
CREATE TABLE #Countries
(Continent VARCHAR(100),
Country VARCHAR(100))
GO
CREATE TABLE #CityList
(Country VARCHAR(100),
City VARCHAR(5000))
GO
INSERT INTO #Countries
VALUES('Europe','France'),('Europe','Germany') 
 
INSERT INTO #CityList
VALUES('France','Paris,Marsilya,Lyon,Lille,Nice'), ('Germany','Berlin,Hamburg,Munih,Frankfurt,Koln')
 
SELECT 
CN.Continent,CN.Country,value
FROM #CityList CL CROSS APPLY string_split(CL.City,',') INNER JOIN 
#Countries CN ON CL.Country = CN.Country
 
 
DROP TABLE IF EXISTS #Countries
GO
DROP TABLE IF EXISTS #CityList
</code></pre>

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. 

Does anyone have a function that returns each one in a row?

Split function equivalent in T-SQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我希望拆分“1，2，3，4，5，6，7，8，9，10，11，12，13，14，15...”(逗号分隔)转换为表或表变量。有没有人有一个函数可以在一行中返回每一个？

问T-SQL中等价的拆分函数？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL中等价的拆分函数？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问T-SQL中等价的拆分函数？
EN