T-SQL中等效的拆分函数?

2022-01-30 00:00:00 sql tsql sql-server-2008 sql-server

我希望将 '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...'(逗号分隔)拆分为表格或表变量.

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?

推荐答案

这里有一些老式的解决方案:

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

在 SQL Server 2008 中，您可以使用 .NET 代码实现相同的目的.也许它会工作得更快，但这种方法肯定更容易管理.

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.

相关文章