SQL Server 自訂函數拆分字串：getstrcount、getstrofindex、getEPnum 實作

T-SQL 字串拆分三部曲：計數、取第 N 段、業務邏輯封裝

SQL Server 2016 前無內建 `STRING_SPLIT`，常見需求以標點分隔的字串 (如 `chatid` 清單) 拆解處理。以下三個標量函數構建基礎工具鏈。

1. getstrcount：計算分隔符出現次數 (即段數 - 1)

CREATE FUNCTION dbo.getstrcount (@str VARCHAR(8000), @delim VARCHAR(10))
RETURNS INT
AS
BEGIN
    DECLARE @count INT = 0, @pos INT = 1;
    WHILE @pos > 0
    BEGIN
        SET @pos = CHARINDEX(@delim, @str, @pos);
        IF @pos > 0 BEGIN SET @count = @count + 1; SET @pos = @pos + LEN(@delim); END
    END
    RETURN @count;
END;

2. getstrofindex：取得第 @index 段 (0-based)

CREATE FUNCTION dbo.getstrofindex (@str VARCHAR(8000), @delim VARCHAR(10), @index INT)
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @start INT = 1, @end INT, @i INT = 0;
    WHILE @i <= @index
    BEGIN
        SET @end = CHARINDEX(@delim, @str, @start);
        IF @end = 0 SET @end = LEN(@str) + 1;
        IF @i = @index RETURN SUBSTRING(@str, @start, @end - @start);
        SET @start = @end + LEN(@delim);
        SET @i = @i + 1;
    END
    RETURN NULL;
END;

3. getEPnum：業務邏輯 — 從 chatid 清單找第一個在線 epnum

CREATE FUNCTION dbo.getEPnum (@chatids VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @ret VARCHAR(8000) = '00000000', @i INT = 0, @cnt INT, @epnum VARCHAR(50), @online BIT;
    IF LEN(@chatids) = 0 RETURN @ret;
    SELECT @cnt = dbo.getstrcount(@chatids, ',');
    WHILE @i < @cnt
    BEGIN
        SELECT @epnum = dbo.getstrofindex(@chatids, ',', @i);
        SELECT @online = online FROM wwchat_user WHERE epnum = @epnum;
        IF @online = 1 BEGIN SET @ret = @epnum; BREAK; END
        SET @i = @i + 1;
    END
    RETURN @ret;
END;

現代替代方案 (SQL Server 2016+)

-- STRING_SPLIT (僅回傳 value，無順序保證)
SELECT value FROM STRING_SPLIT('a,b,c', ',');

-- OPENJSON (需 JSON 格式，保留順序)
SELECT [value] FROM OPENJSON('["a","b","c"]');

-- 數字表 + SUBSTRING (高效能、集合式)
WITH Nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM sys.all_columns)
SELECT SUBSTRING(@str, n, CHARINDEX(',', @str + ',', n) - n) 
FROM Nums WHERE n <= LEN(@str) AND SUBSTRING(',' + @str, n, 1) = ',';

效能建議

• **純量函數逐行執行，效能極差** (每列呼叫一次)，大量資料**嚴禁用於 SELECT 列表/WHERE/JOIN**。
• 改用 **內嵌表值函數 (iTVF)** 或 **CTE 數字表** 集合式寫法，效能提升 100 倍+。
• 若必須封裝邏輯，用 **`CREATE FUNCTION ... RETURNS TABLE`** 並加 `SCHEMABINDING`，優化器可內展開。

結語

舊版相容性下可用純量函數原型驗證邏輯，**上線前必須重寫為 iTVF/集合式查詢**。SQL Server 2016+ 專案直接用 `STRING_SPLIT`/`OPENJSON`，別再手刻拆分函數。