How to Split Strings in T-SQL: A Python `split()` Equivalent?

T-SQL function is equivalent to Python’s split method

There is no built-in function in T-SQL that directly corresponds to the Python split() method. However, several techniques can be used to achieve similar results.

One way is to leverage XML. You can replace the delimiter with <x> and surround the resulting string with <x> tags. Converting the modified string to XML allows you to query individual values ​​as nodes.

<code class="language-sql">DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)</code>

Another approach is to use a recursive CTE to split the string into individual characters.

<code class="language-sql">DECLARE @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
;WITH cte AS
(
    SELECT 0 a, 1 b
    UNION ALL
    SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
    FROM CTE
    WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter) 
    THEN b - a - LEN(@delimiter) 
    ELSE LEN(@str) - a + 1 END) value      
FROM cte WHERE a > 0</code>

For additional options and detailed implementation, see the following resources:

• How to split comma separated string in SQL Server?

The above is the detailed content of How to Split Strings in T-SQL: A Python `split()` Equivalent?. For more information, please follow other related articles on the PHP Chinese website!