SQLSERVER在存储过程中分割字符串并循环操作数据

1、在SQL Server中创建一个用于分割字符串的用户定义函数

CREATE FUNCTION dbo.SplitString
(
    @InputString NVARCHAR(MAX),   -- 输入的字符串
    @Delimiter CHAR(1)            -- 字符串的分隔符
)
RETURNS TABLE
AS
RETURN (
    WITH SplitCTE AS (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
            LTRIM(RTRIM(SUBSTRING(value, 1, LEN(value)))) AS Value
        FROM (
            SELECT 
                CASE 
                    WHEN CHARINDEX(@Delimiter, @InputString) = 0 THEN @InputString
                    ELSE SUBSTRING(@InputString, 1, CHARINDEX(@Delimiter, @InputString) - 1)
                END AS value,
                CASE 
                    WHEN CHARINDEX(@Delimiter, @InputString) = 0 THEN ''
                    ELSE SUBSTRING(@InputString, CHARINDEX(@Delimiter, @InputString) + 1, LEN(@InputString))
                END AS remainder
            FROM (SELECT @InputString AS InputString) AS s
        ) AS SplitData
        UNION ALL
        SELECT 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
            LTRIM(RTRIM(SUBSTRING(value, 1, LEN(value)))) AS Value
        FROM (
            SELECT 
                CASE 
                    WHEN CHARINDEX(@Delimiter, remainder) = 0 THEN remainder
                    ELSE SUBSTRING(remainder, 1, CHARINDEX(@Delimiter, remainder) - 1)
                END AS value,
                CASE 
                    WHEN CHARINDEX(@Delimiter, remainder) = 0 THEN ''
                    ELSE SUBSTRING(remainder, CHARINDEX(@Delimiter, remainder) + 1, LEN(remainder))
                END AS remainder
            FROM SplitCTE
            WHERE remainder <> ''
        ) AS SplitData
    )
    SELECT Value FROM SplitCTE
);

2、在查询中使用它来分割字符串

-- 假设你有一个字符串 "a,b,c,d"
DECLARE @commaSeparatedValues NVARCHAR(MAX) = 'a,b,c,d';

-- 使用 SplitString 函数
SELECT * FROM dbo.SplitString(@commaSeparatedValues, ',');

3、在存储过程中操作

CREATE PROCEDURE YourStoredProcedure
    @commaSeparatedValues NVARCHAR(MAX)
AS
BEGIN
    DECLARE @values TABLE (Value NVARCHAR(50));

    -- 使用内置函数解析并插入到临时表
    INSERT INTO @values (Value)
    SELECT value 
    FROM dbo.SplitString(@commaSeparatedValues, ',');

    -- 遍历并处理每一项数据
    DECLARE @value NVARCHAR(50);
    DECLARE valuesCursor CURSOR FOR
    SELECT Value FROM @values;

    OPEN valuesCursor;
    FETCH NEXT FROM valuesCursor INTO @value;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 在这里添加你的数据处理逻辑
        -- 例如，将所有值转换为大写
        SET @value = UPPER(@value);

        -- 然后插入到目标表
        INSERT INTO YourTable (ItemName)
        VALUES (@value);

        FETCH NEXT FROM valuesCursor INTO @value;
    END;

    CLOSE valuesCursor;
    DEALLOCATE valuesCursor;
END;