使用 XML 在 Sql Server 200820122014 中拆分字符串

由于没有 STRING_SPLIT 函数,我们需要使用 XML hack 将字符串拆分为行:

例:

SELECT split.a.value('.', 'VARCHAR(100)') AS Value 
FROM   (SELECT Cast ('<M>' + Replace('A|B|C', '|', '</M><M>')+ '</M>' AS XML) AS Data) AS A 
       CROSS apply data.nodes ('/M') AS Split(a); 

结果:

+-----+
|Value|
+-----+
|A    |
+-----+
|B    |
+-----+
|C    |
+-----+