好吧，我的代码并不那么简单，但下面是我用来将逗号分隔的输入变量分割为单个值，并将其放入表变量中的代码。我相信您可以稍微修改一下，根据空格进行分割，然后对该表变量执行基本的SELECT查询以获得结果。

-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
    AccountingCycle varchar(10)
)

DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int

SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)

IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
    WHILE @intPosition > 0
    BEGIN
        SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
        IF @vchAccountingCycle <> ''
        BEGIN
            INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
        END
        SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
        SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
    END
END

概念是差不多的。另一种选择是利用SQL Server 2005本身的. net兼容性。实际上，您可以在. net中编写一个简单的方法，将字符串分割，然后将其作为存储过程/函数公开。

基于纯集的解决方案，使用TVF和递归CTE。您可以将此函数JOIN和APPLY到任何数据集。

create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
    select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
    union all
    select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
    , left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
    , [no] + 1 [no]
    from r where value > '')

select ltrim(x) [value], [no] [index] from r where x is not null;
go

用法:

select *
from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
where [index] = 1;

结果:

value   index
-------------
John    1

可以利用Number表进行字符串解析。

创建一个物理数字表:

    create table dbo.Numbers (N int primary key);
    insert into dbo.Numbers
        select top 1000 row_number() over(order by number) from master..spt_values
    go

创建具有1000000行的测试表

    create table #yak (i int identity(1,1) primary key, array varchar(50))

    insert into #yak(array)
        select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
    go

创建函数

    create function [dbo].[ufn_ParseArray]
        (   @Input      nvarchar(4000), 
            @Delimiter  char(1) = ',',
            @BaseIdent  int
        )
    returns table as
    return  
        (   select  row_number() over (order by n asc) + (@BaseIdent - 1) [i],
                    substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
            from    dbo.Numbers
            where   n <= convert(int, len(@Input)) and
                    substring(@Delimiter + @Input, n, 1) = @Delimiter
        )
    go

使用情况(在我的笔记本电脑上40秒内输出3mil行)

    select * 
    from #yak 
    cross apply dbo.ufn_ParseArray(array, ',', 1)

清理

    drop table dbo.Numbers;
    drop function  [dbo].[ufn_ParseArray]

这里的性能并不惊人，但在100万行表上调用函数并不是最好的主意。如果将字符串拆分到多行，我会避免使用该函数。

好吧，我的代码并不那么简单，但下面是我用来将逗号分隔的输入变量分割为单个值，并将其放入表变量中的代码。我相信您可以稍微修改一下，根据空格进行分割，然后对该表变量执行基本的SELECT查询以获得结果。

-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
    AccountingCycle varchar(10)
)

DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int

SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)

IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
    WHILE @intPosition > 0
    BEGIN
        SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
        IF @vchAccountingCycle <> ''
        BEGIN
            INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
        END
        SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
        SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
    END
END

概念是差不多的。另一种选择是利用SQL Server 2005本身的. net兼容性。实际上，您可以在. net中编写一个简单的方法，将字符串分割，然后将其作为存储过程/函数公开。

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

并使用它

select *from dbo.fnSplitString('Querying SQL Server','')

在这里我发布了一个简单的解决方法

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

像这样执行函数

  select * from dbo.split('Hello John Smith',' ')