SQL Server无STRING_SPLIT函数时，如何用XML按冒号分割字符串？

Got it, since your SQL Server version doesn't support STRING_SPLIT and you can't use custom functions or Split_String, here's a reliable XML-based solution to split your colon-separated string into individual values.

Basic Split to Rows #

First, let's take your sample string 1:35410:102001001:102001:10:1 and split it into separate rows:

DECLARE @InputString VARCHAR(1000) = '1:35410:102001001:102001:10:1';

-- Transform the colon-separated string into valid XML
DECLARE @XmlData XML = CAST('<root><item>' + REPLACE(@InputString, ':', '</item><item>') + '</item></root>' AS XML);

-- Extract each value as a row
SELECT 
    T.c.value('.', 'VARCHAR(100)') AS SplitValue
FROM 
    @XmlData.nodes('/root/item') T(c);

How this works:

• We use REPLACE to swap every colon (:) with XML tags </item><item>, wrapping the entire string in a root node to form a valid XML document.
• The nodes() method iterates over each <item> node in the XML, and value() pulls out the text content of each node. You can adjust the data type (like INT if your values are numeric) to match your needs.

Split to Specific Columns #

If you need to map each split value to a dedicated column (since your sample has 6 segments), you can target each XML node directly:

DECLARE @InputString VARCHAR(1000) = '1:35410:102001001:102001:10:1';
DECLARE @XmlData XML = CAST('<root><item>' + REPLACE(@InputString, ':', '</item><item>') + '</item></root>' AS XML);

SELECT
    @XmlData.value('(/root/item)[1]', 'INT') AS Value1,
    @XmlData.value('(/root/item)[2]', 'VARCHAR(20)') AS Value2,
    @XmlData.value('(/root/item)[3]', 'VARCHAR(20)') AS Value3,
    @XmlData.value('(/root/item)[4]', 'VARCHAR(20)') AS Value4,
    @XmlData.value('(/root/item)[5]', 'INT') AS Value5,
    @XmlData.value('(/root/item)[6]', 'INT') AS Value6;

This approach is compatible with SQL Server 2008 and later, no external functions required—just native XML processing built into the engine.

内容的提问来源于stack exchange，提问作者jch