在開發中，很多時候都需要處理拆分字串的操作。下面收集了幾種方法供大家分享，其中的逗號可以改為多種有需要的符號，但是不能針對多種符號同時存在的例子。有待各位補充：

說明：

將字串轉換為正規化的資料表，可以使用多種方法實現，比如前端程式處理、遊標、資料庫迴圈函式，都是常用的技巧。

對於處理這些問題，比較好的思路就是使用SUBSTRING函式取出字串，然後使用CHARINDEX函式定位。最後搭配排序函式完成拆分。

對於2005以後，可以使用CTE來實現。另外可以自定義一個函式處理。搭配輸入分隔符與字串，然後以TABLE方式返回。

解決方法：

下面是這4種方式的示例：

備註：首先針對原始字串，可以使用BULKINSERT 後者BCP命令甚至直接INSERT語句，把原始資料匯入資料表。以下語句是待處理的資料產生指令碼，有業務代號和業務員訂單：

為了輸出每個業務員的每筆訂單序號，所以用迴圈產生一個資料表，儲存指定數量的序號。

接下來就是上面提到的4種方式的實現：

USE tempdb

GO

--建立資料表儲存原始資料：

CREATE TABLE Arrays

   (

      salesID VARCHAR(10) NOT NULL ,

      salesOrd VARCHAR(8000) NOT NULL

    )

GO

--注意：下面insert 語句中逗號後面有一個空格

INSERT  INTO Arrays

VALUES ( 'A', '20, 223, 2544' ) ;

INSERT  INTO Arrays

VALUES ( 'B', '30, 23433, 28' ) ;

INSERT  INTO Arrays

VALUES ( 'C', '12, 10' ) ;

INSERT  INTO Arrays

VALUES ( 'D', '4, 6, 45678, 2' ) ;

GO

 

--通過迴圈產生儲存指定數量的序號的表

CREATE TABLE Nums

   (

      c1 INT NOT NULL

            PRIMARY KEY

    ) ;

GO

--產生資料

DECLARE @i INT

SET @i = 1

WHILE @i <= 8000

    BEGIN

        INSERT  INTO Nums

        VALUES  ( @i )

        SET @i = @i + 1

    END

 

 

--方法一：適合2005以上版本使用。使用ROW_NUBMER函式

 

SELECT  salesID [業務編號] ,

        ROW_NUMBER() OVER ( PARTITION BY salesID ORDER BY c1 ) AS [序號] ,

        SUBSTRING(salesOrd, c1, CHARINDEX(', ', salesOrd + ', ', c1) - c1) AS [值]

FROM    Arrays

        JOIN Nums ON c1 <= LEN(salesOrd)

                     ANDSUBSTRING(', ' + salesOrd, c1, 1) = ', '

ORDER BY salesID ,

        [序號]

       

       

--方法二：適用於任何版本

SELECT  salesID [業務編號] ,

        c1 - LEN(REPLACE(LEFT(salesOrd, c1), ', ', '')) + 1 [序號] ,

        SUBSTRING(salesOrd, c1, CHARINDEX(', ', salesOrd + ', ', c1) - c1) AS [值]

FROM    arrays

        JOIN Nums ON c1 <= LEN(salesOrd)

                     ANDSUBSTRING(', ' + salesOrd, c1, 1) = ', '

ORDER BY salesID ,

        [序號]

       

       

--方式三：適用於2005以上版本，使用CTE實現：

;

WITH    SplitCTE

          AS ( SELECT   salesID ,

                        1 AS pos ,

                        1 AS startpos ,

                        CHARINDEX(', ', salesOrd + ', ') - 1 AS endpos

              FROM    dbo.Arrays

              WHERE   LEN(salesOrd) > 0

              UNION ALL

              SELECT  Prv.salesID ,

                        Prv.pos+ 1 ,

                        Prv.endpos+ 2 ,

                        CHARINDEX(', ', CUR.salesOrd + ', ', Prv.endpos + 2)

                        - 1

              FROM    SplitCTE ASPrv

                        JOIN dbo.Arrays AS Cur ON CUR.salesID = Prv.salesID

                                                 AND CHARINDEX(', ',

                                                             cur.salesOrd

                                                             + ', ',

                                                             Prv.endpos + 2) > 0

            )

    SELECT  A.salesID AS [業務編號] ,

           pos [序號] ,

           CAST(SUBSTRING(salesOrd, startpos, endpos - startpos + 1) AS INT) AS [值]

    FROM    dbo.Arrays AS a

           JOIN SplitCTEAS S ON S.salesID = A.salesID

    ORDER BY A.salesID ,

           pos

 GO

 

    --方法4：使用自定義函式

CREATE FUNCTION dbo.fn_split( @orders AS VARCHAR(MAX) )

RETURNS TABLE

AS

    RETURN

    SELECT  c1 - LEN(REPLACE(LEFT(@orders, c1), ', ', '')) + 1 AS [序號] ,

           SUBSTRING(@orders, c1, CHARINDEX(', ', @orders + ', ', c1) - c1) AS [值]

    FROM    dbo.Nums

    WHERE   c1 <= LEN(@orders)

           AND SUBSTRING(', ' + @orders, c1, 1) = ', ' ;

    GO

   

    --然後使用cross apply技巧，合併分解字串

SELECT  salesID ,

        B.*

FROM    Arrays a

        CROSS APPLY dbo.fn_split(a.salesOrd) b

    GO

通過一下執行計劃的開銷可以看到CTE方法的實現開銷最小，所以建議使用這種方式處理：