T-SQL 拆分使用指定分隔符的字元串(split string)

比如有一個表，我們需要些一個語句像SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......' , 然後就能返回分割成單獨的行。

原表：

| SomeID         | OtherID     | Data

+----------------+-------------+-------------------

| abcdef-.....   | cdef123-... | 18,20,22

| abcdef-.....   | 4554a24-... | 17,19

| 987654-.....   | 12324a2-... | 13,19,20

預期結果：

| OtherID     | SplitData

+-------------+-------------------

| cdef123-... | 18

| cdef123-... | 20

| cdef123-... | 22

| 4554a24-... | 17

| 4554a24-... | 19

在 SQL Server 2016中引入了分割字元串函數STRING_SPLIT（詳細參考MSDN），可以方便的實現。

select OtherID, SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

在SQL Server 2016之前，必須添加一個自定義函數，具體有兩種實現方式.

1. XML解析法 -- 比較容易，適用於字元串能夠轉換為XML（不含有特殊字元也可以將特殊字元替換）

CREATE FUNCTION [dbo].[SplitString]
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN
   ( 
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM
      (
        SELECT x = CONVERT(XML, '<i>'
          + REPLACE(@List, @Delimiter, '</i><i>')
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

2. 遞歸法

create function [dbo].[splitString](@input Varchar(max), @Splitter Varchar(99))
returns table as
Return
  with tmp (DataItem, List , First) as
   (
             select @input  ,@input,   1  --first item ignored, set to get the type right
     union all
     select LEFT(List, CHARINDEX(@Splitter,List+@Splitter)-1),
                                    STUFF(List, 1, CHARINDEX(@Splitter,List+@Splitter), ''),
                                    0
             from tmp
             where List <> ''
   )
 
 
   select DataItem from tmp where first=0

使用方法：

select OtherID, SplitData
from yourtable
cross apply dbo.splitString (Data, ',') cs