SQL Server 字串逗號分割函數 fnSplitStr 完整實現：參數化查詢 IN 條件最佳拍檔

應用場景

前端傳入逗號分隔字串 '張三,李四,王五'，資料庫需轉為結果集供 IN / JOIN 使用。注意：大數據量不建議此模式，應改用表值參數 (TVP) 或暫存表。

函數完整代碼

CREATE FUNCTION [dbo].[fnSplitStr] (
    @sText NVARCHAR(MAX),
    @sDelim CHAR(1)
)
RETURNS @retArray TABLE (value VARCHAR(100))
AS
BEGIN
    DECLARE @posStart BIGINT, @posNext BIGINT, @valLen BIGINT, @sValue NVARCHAR(100)

    IF @sDelim IS NULL
    BEGIN
        IF LEN(@sText) > 100 SET @sText = SUBSTRING(@sText, 1, 100)
        INSERT @retArray (value) VALUES (@sText)
    END
    ELSE
    BEGIN
        SET @posStart = 1
        WHILE @posStart <= LEN(@sText)
        BEGIN
            SET @posNext = CHARINDEX(@sDelim, @sText, @posStart)
            IF @posNext <= 0
                SET @valLen = LEN(@sText) - @posStart + 1
            ELSE
                SET @valLen = @posNext - @posStart

            SET @sValue = SUBSTRING(@sText, @posStart, @valLen)
            SET @posStart = @posStart + @valLen + 1

            IF LEN(@sValue) > 0
            BEGIN
                IF LEN(@sValue) > 100 SET @sValue = SUBSTRING(@sValue, 1, 100)
                INSERT @retArray (value) VALUES (@sValue)
            END
        END
    END
    RETURN
END

使用範例

-- 1. 直接查詢
SELECT * FROM dbo.fnSplitStr(N'張三,李四,王五', N',')

-- 2. 配合 IN 條件
SELECT * FROM Users WHERE UserName IN (SELECT value FROM dbo.fnSplitStr(@Names, N','))

-- 3. 配合 JOIN（效能優於 IN）
SELECT u.* FROM Users u
INNER JOIN dbo.fnSplitStr(@Names, N',') f ON u.UserName = f.value

效能與替代方案對比

STRING_SPLIT 升級範例 (SQL 2016+)

SELECT value FROM STRING_SPLIT(@Names, N',')  -- 簡潔高效，但無順序保證

TVP 完整範例（生產級）

-- 1. 建立類型
CREATE TYPE dbo.NameList AS TABLE (Name NVARCHAR(100) PRIMARY KEY)

-- 2. 存儲過程接收
CREATE PROC usp_GetUsers @Names dbo.NameList READONLY
AS
SELECT u.* FROM Users u INNER JOIN @Names n ON u.UserName = n.Name

-- 3. C# 調用
var dt = new DataTable(); dt.Columns.Add("Name", typeof(string));
names.ForEach(n => dt.Rows.Add(n));
cmd.Parameters.AddWithValue("@Names", dt);

總結

• fnSplitStr 解決舊版本/簡單場景分割需求，邏輯清晰：定位分隔符 → 截取子串 → 迴圈插入表變數。
• SQL 2016+ 專案優先用 STRING_SPLIT 或 OPENJSON。
• 高頻/大數據量介面強制 TVP，從根源解決參數化查詢效能與安全問題。