如何创建一个分割逗号分隔值的SQL函数? [英] How to create a SQL function which splits comma separated value?

问题描述

我想在 SQL Server 中创建一个函数，它采用逗号分隔的字符串作为参数，将其拆分并一次返回一个值.这里的基本思想是从查询中调用该函数.像这样的东西.

I want to create a function in SQL Server which takes a comma separated string a parameter, splits it and returns one value at a time. The basic idea here is to call that function from a query. Something like this.

CREATE FUNCTION SPLIT_VALUE(@IN_CSV)
RETURN VARCHAR AS
 -- LOGIC TO RETURN A SINGLE VALUE FROM CSV
END

我想从存储过程调用这个函数.

I want to call this function from a stored procedure.

CREATE PROCEDURE DEMO_PROC @IN_CSV VARCHAR(5000), @OUT VARCHAR(5000) OUTPUT AS
   BEGIN
     SELECT @OUT= CONCAT(A.VALUE1,B.VALUE2) FROM TABLE1 A INNER JOIN TABLE2 B ON A.ID=B.ID WHERE A.ID 
     IN(--CALL THE FUNCTION AND GET ONE VALUE);
   END;

我每次都必须创建一个循环或游标来指向一个特定的值.这实际上可能吗?如果是，那我该怎么做?

I have to create a loop or cursor to point to a particular value every time. Is this practically possible to? If yes then how can I do that?

推荐答案

就像我提到的，你必须使用 CURSOR 来做到这一点，但是，事实上你想要做到这一点方式推断(大)设计缺陷:

Like I mention, you'll have to use a CURSOR to do this, however, the fact you want to do it this way infers a (large) design flaw:

DECLARE @value varchar(8000)

DECLARE Delimited_Values CURSOR FAST_FORWARD
FOR
SELECT [value]
FROM STRING_SPLIT('a,b,c,d,e',',')

OPEN Delimited_Values;

FETCH NEXT FROM Delimited_Values
INTO @value;

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @value; --Do your stuff here

    FETCH NEXT FROM Delimited_Values
    INTO @value;

END;

CLOSE Delimited_Values;
DEALLOCATE Delimited_Values;

这篇关于如何创建一个分割逗号分隔值的SQL函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！