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

Oracle/SQL Server 中 TRANSLATE 與 REPLACE 差異深度解析

編輯：關於SqlServer

字元級 vs 字串級替換：核心差異一覽

維度	TRANSLATE	REPLACE
替換單位	單一字元 (1:1 映射)	子字串 (整體匹配)
參數結構	`TRANSLATE(str, from_chars, to_chars)`	`REPLACE(str, search_str, replace_str)`
長度不等處理	`from` 比 `to` 長 → 多餘字元刪除	無長度對應概念
NULL 語意	任一參數為 NULL → 回傳 NULL	`search_str` 為 NULL → 回傳 NULL；`replace_str` 為 NULL → 刪除匹配段
支援資料庫	Oracle、PostgreSQL、SQL Server 2017+	所有主流 RDBMS

TRANSLATE 範例解析

-- 將 'abcdefga' 中 a→w, b→o, c→刪除 (因 to_chars 無對應)
SELECT TRANSLATE('abcdefga', 'abc', 'wo') FROM dual;
-- 結果: 'wodefgw'

-- 實務：清洗電話號碼，只保留數字
DELETE FROM phones
WHERE LENGTH(TRANSLATE(TRIM(phone), '0123456789' || phone, '0123456789')) <> LENGTH(TRIM(phone));

邏輯：TRANSLATE(phone, '0123456789'||phone, '0123456789') → 保留數字、刪除非數字。
若結果長度 ≠ 原長度 → 含非數字 → 刪除。

REPLACE 範例解析

-- 子字串整體替換
SELECT REPLACE('fgsgswsgs', 'sg', 'eeerrrttt') FROM dual;
-- 結果: 'fgeeerrrtttsweeerrrttts'

-- 無匹配則原樣回傳
SELECT REPLACE('fgsgswsgs', 'fk', 'j') FROM dual;
-- 結果: 'fgsgswsgs'

對照總結：何時用哪個？

單字元映射/刪除（如全半形轉換、去除特定符號、字元級清洗）→ TRANSLATE。
子字串搜尋替換（如關鍵字高亮、模板變數替換、路徑分隔符統一）→ REPLACE。
SQL Server < 2017 無 TRANSLATE，可用 REPLACE 串聯或 CLR 實現字元級映射。

進階：SQL Server 模擬 TRANSLATE (預 2017)

CREATE FUNCTION dbo.Translate (@str NVARCHAR(MAX), @from NVARCHAR(100), @to NVARCHAR(100))
RETURNS NVARCHAR(MAX)
AS BEGIN
    DECLARE @i INT = 1, @len INT = LEN(@from), @ch NCHAR(1), @map NVARCHAR(2) = @to;
    WHILE @i <= @len
    BEGIN
        SET @ch = SUBSTRING(@from, @i, 1);
        SET @map = CASE WHEN @i <= LEN(@to) THEN SUBSTRING(@to, @i, 1) ELSE N'' END;
        SET @str = REPLACE(@str, @ch, @map);
        SET @i += 1;
    END
    RETURN @str;
END;