将分隔字符串分解为临时表 [英] Break down a delimited string into a temporary table

问题描述

我正在向存储过程"发送一个分隔字符串，该字符串最终将与 IN 语句一起使用以更新某些记录，因此我要做的是创建一个表值函数"来执行此转换和返回可在此更新语句中使用的此表，但我不确定如何执行此操作.

I am sending a delimited string to a 'Stored Procedure' which eventually will be used with an IN statement to update some records, so what im trying to do is create a 'Table-Valued Function' which will do this conversion and return this table that will be usable within this update statement but im unsure how to do this.

还有一个函数可以分解分隔的字符串，但它不喜欢返回整个表，我不太了解它的语法，它不是我以前遇到过的任何 sql ......

There is another function that breaks down delimited strings but it doesnt like like it returns an entire table, i dont really understand its sytax, its not any sql i have come accross before......

所以(我会在下面发布另一个函数)我可以修改或使用这个函数来处理这个分隔字符串中的任意数量的值，还是我需要想出另一种方法?

So (ill post the other function below) can i modify or use this function for an arbitrary amount of values in this delimited string or do i need to come up with another method?

CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

当使用这个函数时，会使用如下语法获取每个项目

when using this function ill get each item with syntax like the following

set @ID = (select Data from dbo.Split(@Params, '|') where ID = 1)

*****根据给定答案的结果更新*******

*****UPDATE WITH RESULTS FROM THE GIVEN ANSWER*******

DECLARE @pVals VARCHAR(MAX)

SET @pvals = '1,2,3,4,5'

DECLARE @tblDelimitedData TABLE (
    [Data] INT NULL
    )

INSERT INTO @tblDelimitedData(data)
    SELECT data
    FROM dbo.Split(@pvals, ',') s
    WHERE ID >= 2

SELECT * FROM @tblDelimitedData

结果是 2、3、4、5

Results are 2, 3, 4, 5

非常感谢！！

推荐答案

要将函数的结果插入到临时表中，您应该使用:

To insert the result of the function to a temporary table, you should use:

INSERT INTO #tempTable(data)
SELECT data
FROM dbo.Split(@Params, '|') s
WHERE ID = 1

使用WHERE ID = 1，但是，只会插入分隔列表中的第一项.如果要过滤然后要插入表中的值的数量，请使用 <=.

Using WHERE ID = 1, however, only inserts the first item in the delimited list. If you want to filter then number of values to be inserted in the table use <=.

比如你只想插入前三个值，那么你使用:

For example, you only want to insert the first three values, then you use:

WHERE ID <= 3

此外，与其他一些可用函数相比，您要拆分的函数速度较慢.我建议您使用基于 Tally 的拆分器，例如 Jeff Moden 的 DelimitedSplit8K.

Additionally, your function to split is slower compared to some other available functions. I recommend that you a Tally-based splitter such as Jeff Moden's DelimitedSplit8K.

这篇关于将分隔字符串分解为临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！