sqlserver2012 字符串拆分

sqlserver2012 将字符串分割

例如 字符串‘1,2,3,4,5’ 为了达到如下效果：

函数如下： 

SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
        FROM( 
            SELECT CAST('<X>'+REPLACE('1,2,3,4,5', ',', '</X><X>')+'</X>' AS XML) AS String
        ) AS A CROSS APPLY String.nodes('/X') AS Split(a)

 

配合 in使用：

select id,title,ctime from table where '12' in (

SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
        FROM( 
            SELECT CAST('<X>'+REPLACE(title, ',', '</X><X>')+'</X>' AS XML) AS String
        ) AS A CROSS APPLY String.nodes('/X') AS Split(a)

)

 

 

sqlserver 2016 字符串拆分，参考该博主文章

https://blog.csdn.net/sqlserverdiscovery/article/details/79187307