Oracle 字串處理實戰技法：LTRIM、RPAD、大小寫控制與 REPLACE 進階應用

場景一：清理髒資料 - 移除尾端空白

問題：前端未 Trim 導致 'DT001 ' (含空格) 與 'DT001' 視為不同值，JOIN/WHERE 失效。

-- 解法：LTRIM/RTRIM/TRIM
SELECT * FROM products WHERE TRIM(prod_code) = 'DT001';

-- 批次修正
UPDATE products SET prod_code = TRIM(prod_code);

場景二：編號擴充 - 左側填充分類碼

需求：舊 5 碼產品編號，新增分類前綴 (B=包材, C=客供, F=輔料) 變 6 碼。

-- RPAD 在右側填充，但需求是左側加字首 → 使用 LPAD 或 串接
UPDATE products
SET prod_code = CASE
    WHEN category = 'PACK' THEN 'B' || prod_code
    WHEN category = 'CUST' THEN 'C' || prod_code
    WHEN category = 'AUX'  THEN 'F' || prod_code
    ELSE prod_code
END
WHERE LENGTH(prod_code) = 5;

-- RPAD 截取應用：取第一碼作為分類標籤
SELECT RPAD(prod_code, 1) AS cat_code FROM products;  -- 實際上 SUBSTR(prod_code,1,1) 更直觀

場景三：報表輸出規範化 - 大小寫統一

-- 批次修正產品代碼為大寫
UPDATE products SET prod_code = UPPER(prod_code) WHERE prod_code != UPPER(prod_code);

場景四：複雜字串替換 - 行政區域更名

需求：將地址欄位中的 '下沙市' 統一改為 '下沙區'，不影響 '杭州市'。

-- REPLACE 精確替換
UPDATE customer_address
SET address = REPLACE(address, '下沙市', '下沙區')
WHERE address LIKE '%下沙市%';

-- 組合技：先擷取再替換 (產品代碼轉描述)
SELECT REPLACE(SUBSTR(prod_code, 2, 2), 'SD', '手動工具') AS tool_type
FROM products
WHERE SUBSTR(prod_code, 1, 1) = 'P';

進階組合招式

-- 1. 遮罩敏感資料 (身分證/電話)
SELECT RPAD(SUBSTR(id_no, 1, 3), LENGTH(id_no), '*') AS masked_id FROM customers;

-- 2. 字串分割 (以逗號為例)
WITH t(str) AS (SELECT 'A,B,C' FROM dual)
SELECT TRIM(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) token
FROM t CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1;

-- 3. 多重替換 (TRANSLATE 單字元映射)
SELECT TRANSLATE(phone, '()- ', '') AS clean_phone FROM contacts;

-- 4. 模糊比對 (同音異寫)
SELECT * FROM customers WHERE SOUNDEX(name) = SOUNDEX('Johnson');

效能與陷阱提醒

• 避免在 WHERE 條件欄位上使用函數：WHERE UPPER(name) = 'JOHN' 導致全表掃描 → 建立 Function-Based Index CREATE INDEX idx_upper_name ON customers(UPPER(name))
• REPLACE 大量更新時分批提交，避免 Undo 爆滿
• INITCAP 對特殊字元 (如 O'Neil → O'Neil) 處理符合直覺，但非英文語系需測試
• 字串操作盡量在應用層完成，資料庫專注集合運算