Oracle 文字處理函數實戰：大小寫、長度、擷取、搜尋、修剪

大小寫轉換三劍客

SELECT UPPER('abc'), LOWER('ABC'), INITCAP('hello world') FROM dual;
-- UPPER  LOWER  INITCAP
-- ABC    abc    Hello World

• INITCAP：以空白、標點、控制字元為界，將每個單字首字母大寫，其餘小寫。適合整理髒資料。

長度計算

SELECT product_name, LENGTH(product_name) AS char_len,
       LENGTHB(product_name) AS byte_len
FROM product
ORDER BY product_name;

• LENGTH：字元數（多位元組字元算 1）。
• LENGTHB：位元組數（UTF-8 中文通常 3 bytes）。

子字串擷取：SUBSTR / SUBSTRB

-- 取編號：LA-101 → 101
SELECT SUBSTR(item_id, 4, 3) AS item_num, item_desc
FROM item_test;

-- SUBSTRB 以位元組計算起始與長度，適合定長位元組切割

字串搜尋：INSTR / INSTRB

-- 找 'line' 在第幾個字元（從第 1 字元找）
SELECT INSTR('this is line one', 'line', 1) FROM dual;  --> 9

-- 找逗號位置
SELECT item_desc, INSTR(item_desc, ',', 1) AS comma_pos
FROM item_test;

• 回傳 0 表示未找到。
• 第三參數為負數時，從右向左搜尋，但回傳位置仍以左起計算。

修剪空白與指定字元：LTRIM / RTRIM / TRIM

SELECT LTRIM('  abc  ') AS l, RTRIM('  abc  ') AS r, TRIM('  abc  ') AS t FROM dual;
-- l='abc  '  r='  abc'  t='abc'

-- 修剪指定字元集合
SELECT LTRIM('xyxxDWEYExyyx', 'xy') AS l, RTRIM('xyxxDWEYExyyx', 'xy') AS r FROM dual;
-- l='DWEYExyyx'  r='xyxxDWEYE'

-- 標準 TRIM 語法
SELECT TRIM(LEADING 'x' FROM 'xxabcxx') AS l,
       TRIM(TRAILING 'x' FROM 'xxabcxx') AS r,
       TRIM(BOTH 'x' FROM 'xxabcxx') AS b
FROM dual;
-- l='abcxx'  r='xxabc'  b='abc'

綜合實戰：解析「品項,規格」格式

WITH t AS (
  SELECT 'Can, Small' AS desc FROM dual UNION ALL
  SELECT 'Bottle, Large' FROM dual
)
SELECT desc,
       SUBSTR(desc, 1, INSTR(desc, ',')-1) AS item,
       TRIM(SUBSTR(desc, INSTR(desc, ',')+1)) AS spec
FROM t;

效能與避坑提醒

• 避免在 WHERE 條件對大量資料欄位使用函數（如 WHERE UPPER(name) = 'JOHN'），會導致全表掃描 → 改用函數索引或存儲大寫影子欄。
• TRIM 僅支援單一字元；多字元需疊加 LTRIM/RTRIM。
• 多位元組環境下，SUBSTRB/INSTRB 才能精確對齊位元組邊界（如讀取二進位協定）。