mysql - 创建一个函数将分隔字符串拆分到表中

Question

为了分割分隔字符串，我需要创建一个返回表并​​采用逗号分隔字符串作为参数的函数。

例如，看看这个输入表:

使用该函数，结果将是:

在本例中，分隔字符串是应作为参数的 Person 列。

提前致谢。

Answer 1

试试这个

    --Build Schema for Your Case

    DECLARE @TAB TABLE (ID INT, NAME VARCHAR(1000))
    INSERT INTO @TAB
    SELECT 1,'Pravesh,Simon'
    UNION ALL
    SELECT 2,'Me,Myself,I'


    --Used Recursive CTE to achieve Tabular view of Comma Separated Column
    --Used While loop to take next row
    --Used Table Variable @RESULT to store your Result Set

    DECLARE @ID INT,  @T VARCHAR(100), @SNO INT= 1, @MAX_SNO INT
    DECLARE @RESULT TABLE (ID INT, NAME VARCHAR(1000))


    SELECT @SNO=1, @MAX_SNO =MAX(ID) FROM @TAB

    WHILE(@SNO<=@MAX_SNO)
    BEGIN
    SELECT @ID = ID, @T = NAME FROM @TAB WHERE ID=@SNO
    SET @T =@T+','


    ;WITH MyCTE(Start,[End]) AS(

    SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
    UNION ALL
    SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] 
    from MyCTE where [End]<LEN(@T)
    )

    INSERT INTO @RESULT

    Select @SNO AS ID, SUBSTRING(@T,Start,[End]-Start) NAME from MyCTE;


    SET @SNO+=1
    END

    --Getting Result Set

    SELECT * FROM @RESULT

编辑:来自您的评论

如果你想用一个函数来完成上述操作，这个比上面的循环容易得多。

    CREATE FUNCTION [DBO].[FN_SPLIT_STR_TO_COL] (@T AS VARCHAR(4000) )
    RETURNS
     @RESULT TABLE(VALUE VARCHAR(250))
    AS
    BEGIN
         SET @T= @T+','
           ;WITH MYCTE(START,[END]) AS(

        SELECT 1 AS START,CHARINDEX(',',@T,1) AS [END]
        UNION ALL
        SELECT [END]+1 AS START,CHARINDEX(',',@T,[END]+1)AS [END] 
        FROM MYCTE WHERE [END]<LEN(@T)
        )
        INSERT INTO @RESULT 
        SELECT SUBSTRING(@T,START,[END]-START) NAME FROM MYCTE;

          RETURN 
    END

现在只需将列传递给每一行即可调用该函数。

使用交叉应用

SELECT ID,FN_RS.VALUE FROM @TAB
CROSS APPLY
(SELECT * FROM [DBO].[FN_SPLIT_STR_TO_COL] (NAME)) AS FN_RS