Microsoft SQL Server Split String function in Sql Server Split string in Sql Server 2008/2012/2014 using XML
Example
Since there is no STRING_SPLIT function we need to use XML hack to split the string into rows:
Example:
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);
Result:
+-----+
|Value|
+-----+
|A |
+-----+
|B |
+-----+
|C |
+-----+
