Oracle INSTR 與 SQL Server CHARINDEX 函數對照與移植指南

前言

跨資料庫平台移植應用時，字串搜尋函數是常見差異點。Oracle 使用 INSTR，SQL Server 使用 CHARINDEX，兩者參數順序、功能細節不同，直接導致 SQL 語句移植失敗。本文詳細對照兩者語法、行為差異，提供移植對照表與通用寫法建議。

一、Oracle INSTR 函數

語法

INSTR( source_string, search_string [, position [, occurrence ]] )

回傳值

• 找到：回傳第 occurrence 次出現的起始位置（從 1 起算）。
• 未找到：回傳 0。
• position 為負數時，從右往左搜尋，但回傳位置仍以左邊為基準計算。

範例

SELECT INSTR('CORPORATE FLOOR', 'OR', 3, 2) FROM dual;  -- 14 (第2個OR，從第3字元開始找)
SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) FROM dual; -- 14 (從右往左找第1個)
SELECT INSTR('Hello World', 'o', -1, 1) FROM dual;     -- 8 (從右找第一個o)

二、SQL Server CHARINDEX 函數

語法

CHARINDEX( search_string, source_string [, start_location ] )

回傳值

• 找到：回傳起始位置（從 1 起算）。
• 未找到：回傳 0。
• 不支援指定第 N 次出現，也不支援從右往左搜尋。

範例

SELECT CHARINDEX('SQL', 'Microsoft SQL Server');           -- 11
SELECT CHARINDEX('7.0', 'Microsoft SQL Server 2000');      -- 0 (不存在)
SELECT CHARINDEX('o', 'Hello World', 5);                   -- 8 (從第5字元開始找)

三、核心差異對照表

四、移植對照與替代方案

場景 1：單純判斷字串是否包含子字串

Oracle： INSTR(col, 'abc') > 0
SQL Server： CHARINDEX('abc', col) > 0
通用寫法 (ANSI SQL)： col LIKE '%abc%'

場景 2：指定起始位置搜尋

Oracle： INSTR(col, 'abc', 10)
SQL Server： CHARINDEX('abc', col, 10)

場景 3：尋找第 N 次出現 (Oracle 專有)

SQL Server 無直接對應函數，需改寫：

-- 方法 A：遞迴 CTE (SQL Server 2008+)
WITH cte AS (
    SELECT 1 AS pos, CHARINDEX('abc', col) AS found
    UNION ALL
    SELECT found + 1, CHARINDEX('abc', col, found + 1)
    FROM cte WHERE found > 0
)
SELECT found FROM cte WHERE pos = 3; -- 找第3次

-- 方法 B：應用層處理 / CLR 函數 / 將邏輯下推至應用程式

場景 4：從右往左搜尋 (Oracle 專有)

SQL Server 可使用 REVERSE 配合 CHARINDEX 模擬：

-- Oracle: INSTR(str, 'abc', -1, 1) 找最後一個
-- SQL Server 模擬：
SELECT LEN(str) - CHARINDEX('cba', REVERSE(str)) + 1 AS last_pos
FROM (SELECT 'abcXYZabc' AS str) t;
-- 注意：子字串也需反轉 ('abc' -> 'cba')

五、建立通用函數封裝 (建議)

在應用程式資料存取層 (DAL) 封裝平台無關的搜尋方法：

// Java 範例
public int findString(String source, String target, int startPos, int occurrence) {
    if (isOracle()) {
        // 呼叫 INSTR
    } else if (isSqlServer()) {
        // 呼叫 CHARINDEX + 邏輯處理 occurrence
    }
    // 其他資料庫...
}

或在資料庫端建立同名函數：

-- SQL Server 建立 INSTR 相容函數
CREATE FUNCTION dbo.INSTR (
    @source NVARCHAR(MAX), @search NVARCHAR(MAX),
    @position INT = 1, @occurrence INT = 1
)
RETURNS INT
AS BEGIN
    DECLARE @pos INT = @position, @found INT = 0, @cnt INT = 0;
    IF @position < 0 BEGIN
        SET @source = REVERSE(@source);
        SET @search = REVERSE(@search);
        SET @pos = 1;
    END
    WHILE @cnt < @occurrence BEGIN
        SET @found = CHARINDEX(@search, @source, @pos);
        IF @found = 0 BREAK;
        SET @cnt = @cnt + 1;
        SET @pos = @found + 1;
    END
    IF @position < 0 AND @found > 0
        RETURN LEN(@source) - @found - LEN(@search) + 2;
    RETURN @found;
END

六、其他相關字串函數對照

建議：大型移植專案建立「SQL 方言轉換層」或使用 ORM 框架 (Hibernate, MyBatis) 吸收差異。針對字串搜尋邏輯，優先使用 LIKE 或應用層處理，減少資料庫專有函數依賴。