MySQL 将值拆分为行。创建函数时出错

Question

我想将列中的分隔字符串分隔成行。例如

ID Value           ID Value  
---------          ----------  
1  a,b     into    1  a  
2  c,d             1  b  
                   2  c  
                   2  d  

我已经尝试过这些解决方案

1.

create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','&amp; '),'<','&lt;'), @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )

错误:
1. 第一行 [ from [dbo] 处的语法错误。
2. 有时错误是在 RETURNS 而不是。

2.

SELECT T1.reference, T1.name, T2.my_Splits AS subjects, T1.subtitile
FROM
 (
  SELECT *,
  CAST('<X>'+replace(T.subjects,',','</X><X>')+'</X>' as XML) as my_Xml 
  FROM archive T
 ) T1
 CROSS APPLY
 ( 
 SELECT my_Data.D.value('.','varchar(50)') as my_Splits
 FROM T1.my_Xml.nodes('X') as my_Data(D)
 ) T2

错误:
1. CAST 行中来自 AS XML 的 XML 语法错误。
2. CROSS APPLY 语法错误。它说缺少 JOIN。

3.

SELECT A.[State],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [State],  
         CAST ('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);  

错误:
( 来自 Split.a.value(....

我是 SQL 的新手，无法完全理解 Internet 上提供的这些解决方案。它似乎对其他人都适用，但我不知道为什么我不断收到这些语法错误。

Answer 1

在单个列中将数据存储为 CSV 确实是一种糟糕的方法。考虑改变你的表设计

SELECT id,
       Split.a.value('.', 'VARCHAR(100)') Value
FROM   (SELECT ID,
               Cast ('<M>' + Replace(Value, ',', '</M><M>') + '</M>' AS XML) AS Data
        FROM   archive) AS A
       CROSS APPLY Data.nodes ('/M') AS Split(a) 

SqlFiddle Demo

如果你需要一个函数，那么使用这个。

ALTER FUNCTION [dbo].[Split] (@sep VARCHAR(32),
                              @s   VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
      (SELECT Split.a.value('.', 'VARCHAR(100)') Value
       FROM   (SELECT Cast ('<M>' + Replace(@sep, @s, '</M><M>') + '</M>' AS XML) AS Data) AS A
              CROSS APPLY Data.nodes ('/M') AS Split(a)) 


SELECT id,
       cs.value
FROM   yourtable
       CROSS apply [dbo].[Split] (Value, ',') cs