DB2 實作 REVERSE 字串反轉函數：相容 Oracle 與 SQL Server 行為

前言

Oracle 與 SQL Server 內建 REVERSE() 函數可將字串字元順序反轉，但 DB2 (LUW) 至 11.5 版仍未內建。本文分析 Oracle/SQL Server 的型別語意，並提供 DB2 相容實作，含型別定義、效能考量與測試驗證。

一、Oracle REVERSE 行為分析

SQL> SELECT REVERSE('1234') FROM dual;     -- 4321
SQL> SELECT REVERSE('1234   ') FROM dual; -- '   4321' (保留尾端空白)
SQL> SELECT LENGTH(REVERSE('1234   ')) FROM dual; -- 8 (CHAR 型別固定長度)

結論：Oracle REVERSE 參數與回傳皆為 CHAR (固定長度)，尾端空白保留。

二、SQL Server REVERSE 行為分析

> SELECT REVERSE('1234');    -- 4321 (VARCHAR, 無尾端空白)
> SELECT REVERSE(1234);      -- 隱性轉型為 VARCHAR 再反轉

結論：SQL Server 參數/回傳為 VARCHAR/NVARCHAR，支援隱性數值轉字串。

三、DB2 相容實作

採用 VARCHAR 輸入/輸出 (符合現代應用習慣)，支援隱性數值轉型：

CREATE OR REPLACE FUNCTION REVERSE (p_str VARCHAR(200))
RETURNS VARCHAR(200)
SPECIFIC REVERSE_FUNC
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
    DECLARE v_result VARCHAR(200) DEFAULT '';
    DECLARE v_idx    INT;

    SET v_idx = LENGTH(RTRIM(p_str));  -- 忽略尾端空白，模擬 VARCHAR 行為
    WHILE v_idx >= 1 DO
        SET v_result = v_result || SUBSTR(p_str, v_idx, 1);
        SET v_idx = v_idx - 1;
    END WHILE;
    RETURN v_result;
END @

關鍵點：

• DETERMINISTIC：相同輸入必得相同輸出，允許優化器快取結果。
• NO EXTERNAL ACTION：無副作用，可並行執行。
• 使用 RTRIM 移除尾端空白，模擬 VARCHAR 語意；若需完全相容 Oracle CHAR 行為，請移除 RTRIM 並將回傳型別改為 CHAR(200)。

四、測試驗證

-- 字串反轉
VALUES REVERSE('123456');          -- 654321

-- 數值隱性轉型 (DB2 支援隱性 CAST)
VALUES REVERSE(1234);              -- 4321

-- 空字串
VALUES REVERSE('');                -- ''

-- 含空白
VALUES REVERSE('  hello  ');       -- '  olleh  ' (VARCHAR 行為保留前導/內部空白)

五、效能優化建議

1. 改用遞歸 CTE (DB2 10.5+ 支援遞歸 CTE) 避免 WHILE 迴圈逐字串連接的效能損耗：

CREATE OR REPLACE FUNCTION REVERSE_CTE (p_str VARCHAR(200))
RETURNS VARCHAR(200)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
    RETURN (
        WITH RECURSIVE rev(pos, ch, out_str) AS (
            SELECT LENGTH(RTRIM(p_str)), SUBSTR(p_str, LENGTH(RTRIM(p_str)), 1), CAST('' AS VARCHAR(200))
            UNION ALL
            SELECT pos-1, SUBSTR(p_str, pos-1, 1), out_str || ch
            FROM rev WHERE pos > 1
        )
        SELECT out_str FROM rev WHERE pos = 1
    );
END @

2. 大量資料轉換 時，建議在應用層或 ETL 階段完成反轉，避免 SQL 執行期逐列呼叫 UDF 的額外負擔。

六、結語

透過自訂 SQL UDF 即可在 DB2 實作相容 Oracle/SQL Server 的 REVERSE() 函數。建議採用 VARCHAR 語意並加上 DETERMINISTIC、NO EXTERNAL ACTION 以利優化器；極大量資料場景請考慮 CTE 遞歸或應用層處理。