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

SQL Server 自訂函數集合：字串分割、數值轉換與實用工具

編輯：關於SqlServer

高效能字串分割函數 (Table-Valued Function)

將分隔字串轉換為資料列集，支援多字元分隔符號、空值處理、效能優化。

1. 通用字串分割函數 (Inline TVF - 最佳效能)

CREATE FUNCTION dbo.fn_SplitString
(
    @InputString NVARCHAR(MAX),
    @Delimiter   NVARCHAR(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    -- 使用 XML 技巧分割 (SQL Server 2005+)
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ItemIndex,
        LTRIM(RTRIM(Split.a.value('.', 'NVARCHAR(MAX)'))) AS ItemValue
    FROM
    (
        SELECT CAST('' + REPLACE(@InputString, @Delimiter, '') + '' AS XML) AS Data
    ) AS A
    CROSS APPLY Data.nodes('/M') AS Split(a)
    WHERE @InputString IS NOT NULL AND @InputString <> ''
);
GO

-- 使用範例
SELECT * FROM dbo.fn_SplitString('Apple,Orange,Banana,Grape', ',');
-- 結果: 1 Apple / 2 Orange / 3 Banana / 4 Grape

2. 高效能 Numbers Table 分割法 (大量資料優化)

-- 建立數字輔助表 (一次性建立，永久使用)
CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY);
WITH N AS (SELECT 1 AS n UNION ALL SELECT n+1 FROM N WHERE n < 10000)
INSERT INTO dbo.Numbers SELECT n FROM N OPTION (MAXRECURSION 10000);

-- 分割函數 (效能極佳，無 XML 開銷)
CREATE FUNCTION dbo.fn_SplitString_Fast
(
    @InputString NVARCHAR(MAX),
    @Delimiter   NVARCHAR(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY n.Number) AS ItemIndex,
        SUBSTRING(@InputString, n.Number, 
            CHARINDEX(@Delimiter, @InputString + @Delimiter, n.Number) - n.Number) AS ItemValue
    FROM dbo.Numbers n
    WHERE n.Number <= LEN(@InputString)
      AND SUBSTRING(@Delimiter + @InputString, n.Number, LEN(@Delimiter)) = @Delimiter
);
GO

3. JSON 陣列解析函數 (SQL Server 2016+)

CREATE FUNCTION dbo.fn_ParseJsonArray
(
    @JsonArray NVARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        [key] + 1 AS ItemIndex,
        [value] AS ItemValue,
        [type] AS JsonType
    FROM OPENJSON(@JsonArray)
);
GO

-- 使用範例
SELECT * FROM dbo.fn_ParseJsonArray('["Apple", "Orange", "Banana"]');
-- 結果: 1 Apple / 2 Orange / 3 Banana

4. 實用工具函數集

數值格式化：中文大寫金額

CREATE FUNCTION dbo.fn_NumberToChineseCurrency
(
    @Amount DECIMAL(18,2)
)
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @Digits NVARCHAR(20) = N'零壹貳參肆伍陸柒捌玖';
    DECLARE @Units NVARCHAR(20) = N'分角元拾佰仟萬拾佰仟億';
    DECLARE @Result NVARCHAR(200) = N'';
    DECLARE @IntPart BIGINT = FLOOR(@Amount);
    DECLARE @DecPart INT = ROUND((@Amount - @IntPart) * 100, 0);
    DECLARE @Pos INT = 0;
    
    IF @Amount = 0 RETURN N'零元整';
    
    -- 整數部分
    WHILE @IntPart > 0
    BEGIN
        SET @Result = SUBSTRING(@Digits, (@IntPart % 10) + 1, 1) + 
                      SUBSTRING(@Units, @Pos + 3, 1) + @Result;
        SET @IntPart = @IntPart / 10;
        SET @Pos = @Pos + 1;
    END
    
    -- 小數部分
    IF @DecPart > 0
    BEGIN
        SET @Result = @Result + SUBSTRING(@Digits, (@DecPart / 10) + 1, 1) + N'角' +
                      SUBSTRING(@Digits, (@DecPart % 10) + 1, 1) + N'分';
    END
    ELSE
        SET @Result = @Result + N'整';
    
    RETURN @Result;
END;
GO

-- 測試
SELECT dbo.fn_NumberToChineseCurrency(12345.67); 
-- 結果: 壹萬貳仟參佰肆拾伍元陸角柒分

移除 HTML 標籤

CREATE FUNCTION dbo.fn_StripHtml
(
    @Html NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Start INT, @End INT, @Length INT;
    SET @Html = REPLACE(@Html, '<', '<');  -- 先編碼避免無限迴圈
    SET @Start = CHARINDEX('<', @Html);
    WHILE @Start > 0
    BEGIN
        SET @End = CHARINDEX('>', @Html, @Start);
        IF @End = 0 BREAK;
        SET @Html = STUFF(@Html, @Start, @End - @Start + 1, '');
        SET @Start = CHARINDEX('<', @Html);
    END
    RETURN REPLACE(REPLACE(@Html, '<', '<'), '>', '>');
END;
GO

驗證身分證字號 (台灣)

CREATE FUNCTION dbo.fn_ValidateTwId
(
    @Id CHAR(10)
)
RETURNS BIT
AS
BEGIN
    IF @Id NOT LIKE '[A-Z][12][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' RETURN 0;
    
    DECLARE @Letters NVARCHAR(26) = 'ABCDEFGHJKLMNPQRSTUVXYWZIO'; -- 對應 10-35
    DECLARE @FirstChar CHAR(1) = UPPER(LEFT(@Id, 1));
    DECLARE @LetterValue INT = CHARINDEX(@FirstChar, @Letters) + 9; -- A=10, B=11...
    DECLARE @Sum INT = (@LetterValue / 10) + (@LetterValue % 10) * 9;
    DECLARE @Weights INT = 8;
    DECLARE @i INT = 2;
    
    WHILE @i <= 9
    BEGIN
        SET @Sum = @Sum + CAST(SUBSTRING(@Id, @i, 1) AS INT) * @Weights;
        SET @Weights = @Weights - 1;
        SET @i = @i + 1;
    END
    
    SET @Sum = @Sum + CAST(RIGHT(@Id, 1) AS INT);
    RETURN CASE WHEN @Sum % 10 = 0 THEN 1 ELSE 0 END;
END;
GO

產生亂數字串 (指定長度、字元集)

CREATE FUNCTION dbo.fn_GenerateRandomString
(
    @Length INT = 10,
    @Charset NVARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @Result NVARCHAR(100) = N'';
    DECLARE @CharsetLen INT = LEN(@Charset);
    DECLARE @i INT = 0;
    
    WHILE @i < @Length
    BEGIN
        SET @Result += SUBSTRING(@Charset, 
            CAST(RAND(CHECKSUM(NEWID())) * @CharsetLen) + 1, 1);
        SET @i += 1;
    END
    RETURN @Result;
END;
GO

效能比較與選擇建議

函數類型	適用場景	效能	相依性
XML 分割	一般用途、字串長度 < 100KB	中等	無額外物件
Numbers Table 分割	高頻呼叫、長字串、大量資料	最佳	需建立 Numbers 表
OPENJSON 分割	JSON 格式輸入、SQL Server 2016+	優	版本需求
CLR 分割	極高效能需求、複雜分割邏輯	極佳	需啟用 CLR、部署組件