使用 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 |
+-----+