您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 6 大實用自訂函數：提取數字/英文/中文、過濾重複字元、字串分割

編輯：關於SqlServer

實務開發常需從混雜字串中提取特定字元類型、去除重複字元或依分隔符分割字串。本文整理 6 大高頻自訂純量函數，涵蓋提取數字、英文、中文、判斷含中文、過濾重複字元 (兩種演算法)、依指定分隔符分割取第 N 段，附完整建立腳本與測試範例。

一、判斷/提取中文字元

1. 判斷字串是否含中文並提取

CREATE FUNCTION dbo.fun_getCN(@str NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @word NCHAR(1), @CN NVARCHAR(4000) = '';
    WHILE LEN(@str) > 0
    BEGIN
        SET @word = LEFT(@str, 1);
        IF UNICODE(@word) BETWEEN 19968 AND 19968 + 20901  -- CJK 統一表意文字範圍
            SET @CN = @CN + @word;
        SET @str = RIGHT(@str, LEN(@str) - 1);
    END
    RETURN @CN;
END
GO

-- 測試
SELECT dbo.fun_getCN('ASDKG論壇KDL');  -- 回傳 '論壇'
SELECT dbo.fun_getCN('ASDKDL');        -- 回傳 ''

二、提取數字

CREATE FUNCTION dbo.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @S) > 0
        SET @S = STUFF(@S, PATINDEX('%[^0-9]%', @S), 1, '');
    RETURN @S;
END
GO

-- 測試
PRINT dbo.GET_NUMBER2('呵呵ABC123ABC');  -- 123

三、提取英文

CREATE FUNCTION dbo.GET_STR(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    WHILE PATINDEX('%[^a-z]%', @S) > 0
        SET @S = STUFF(@S, PATINDEX('%[^a-z]%', @S), 1, '');
    RETURN @S;
END
GO

-- 測試
PRINT dbo.GET_STR('呵呵ABC123ABC');  -- ABCABC

四、提取中文 (範圍法)

CREATE FUNCTION dbo.CHINA_STR(@S NVARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    WHILE PATINDEX('%[^吖-座]%', @S) > 0
        SET @S = STUFF(@S, PATINDEX('%[^吖-座]%', @S), 1, N'');
    RETURN @S;
END
GO

-- 測試
PRINT dbo.CHINA_STR(N'呵呵ABC123ABC');  -- 呵呵

五、過濾重複字元 (兩種演算法)

方法 1：依分隔符去重 (保留順序)

CREATE FUNCTION dbo.DISTINCT_STR(@S NVARCHAR(100), @SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    IF @S IS NULL RETURN NULL;
    DECLARE @NEW VARCHAR(50) = '', @INDEX INT, @TEMP VARCHAR(50);
    -- 確保首尾有分隔符
    IF LEFT(@S, 1) <> @SPLIT SET @S = @SPLIT + @S;
    IF RIGHT(@S, 1) <> @SPLIT SET @S = @S + @SPLIT;
    WHILE CHARINDEX(@SPLIT, @S) > 0 AND LEN(@S) <> 1
    BEGIN
        SET @INDEX = CHARINDEX(@SPLIT, @S);
        SET @TEMP = LEFT(@S, CHARINDEX(@SPLIT, @S, @INDEX + LEN(@SPLIT)));
        IF @NEW = '' SET @NEW = @TEMP;
        ELSE SET @NEW = @NEW + REPLACE(@TEMP, @SPLIT, '') + @SPLIT;
        WHILE CHARINDEX(@TEMP, @S) > 0
            SET @S = STUFF(@S, CHARINDEX(@TEMP, @S) + LEN(@SPLIT),
                           CHARINDEX(@SPLIT, @S, CHARINDEX(@TEMP, @S) + LEN(@SPLIT)) - CHARINDEX(@TEMP, @S), '');
    END
    RETURN RIGHT(LEFT(@NEW, LEN(@NEW) - 1), LEN(LEFT(@NEW, LEN(@NEW) - 1)) - 1);
END
GO

-- 測試
PRINT dbo.DISTINCT_STR('A,A,B,C,C,B,C,', ',');  -- A,B,C

方法 2：字元級去重 (不保留順序，效能較好)

CREATE FUNCTION dbo.DISTINCT_STR2(@S VARCHAR(8000))
RETURNS VARCHAR(100)
AS
BEGIN
    IF @S IS NULL RETURN NULL;
    DECLARE @NEW VARCHAR(50) = '';
    WHILE LEN(@S) > 0
    BEGIN
        SET @NEW = @NEW + LEFT(@S, 1);
        SET @S = REPLACE(@S, LEFT(@S, 1), '');
    END
    RETURN @NEW;
END
GO

-- 測試
SELECT dbo.DISTINCT_STR2('AABCCD');  -- ABCD (順序不保證)

六、依分隔符分割取第 N 段

CREATE FUNCTION dbo.SPLIT_STR(
    @S VARCHAR(8000),     -- 來源字串
    @INDEX INT,           -- 要取第幾段 (1-based)
    @SPLIT VARCHAR(10)    -- 分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
    IF @S IS NULL RETURN NULL;
    DECLARE @SPLITLEN INT = LEN(@SPLIT + 'A') - 2;
    WHILE @INDEX > 1 AND CHARINDEX(@SPLIT, @S + @SPLIT) > 0
        SELECT @INDEX = @INDEX - 1,
               @S = STUFF(@S, 1, CHARINDEX(@SPLIT, @S + @SPLIT) + @SPLITLEN, '');
    RETURN ISNULL(LEFT(@S, CHARINDEX(@SPLIT, @S + @SPLIT) - 1), '');
END
GO

-- 測試
PRINT dbo.SPLIT_STR('AA|BB|CC', 2, '|');  -- BB

效能與適用性建議

函數	適用場景	效能備註
GET_NUMBER2/GET_STR/CHINA_STR	資料清洗、欄位標準化	WHILE 迴圈逐字元處理，大量資料建議改用 CLR 或應用層處理
DISTINCT_STR	分隔符字串去重 (保留順序)	邏輯複雜，適合較短字串
DISTINCT_STR2	簡單字元去重	REPLACE 全掃描，長字串效能下降快
SPLIT_STR	單一分割取值	不支援一次分割輸出資料表，大量分割建議使用 STRING_SPLIT (2016+) 或 XML/JSON 方案