PLSQL取代中文字只取英文字Regular Expression

目的

只保留繁體中文和英文的方法如下(底線需另外處理)：

select upper(REGEXP_REPLACE('原字串', '\W|_', '')) from dual

但這次是討論如何將Query的資料取出大小寫英文字，其餘一律捨棄。

方法

-- 完整語法：REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )
select REGEXP_REPLACE( 'CHEN CHIa陳JinＡｂＺ%123','[^a-zA-Zａ-ｚＡ-Ｚ]', '')
from dual

會得到：CHENCHIaJinＡｂＺ。(此處連全形英文都保留)

進階應用

搭配SOUNDEX()歸納相似的英文名稱，但由於

The SOUNDEX function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.

因此原本所填的空白要保留，以便搭配INSTR()和SUBSTR()區分第幾個單字，所以使用REPLACE()，如：

select replace(
         REGEXP_REPLACE( 'az AZ 123 ａｚ ＡＺ 符$#@','[^a-zA-Zａ-ｚＡ-Ｚ ]', '')
        ,' ',' ')
from dual

此處可用TO_SINGLE_BYTE()先將全形轉半形，就只需比對半形英文，結果得到：「az AZ ａｚ ＡＺ 」，如果字尾的空白字元不要的話，就在外層加上TRIM()即可。

最後小波再提供搭配SOUNDEX()和SUBSTR()範例：

select soundex(substr('ABC def Hij', 1, INSTR('ABC def Hij', ' ', 1, 1))), -- 第1個單字
       soundex(substr('ABC def Hij', -- 第2個單字
               INSTR('ABC def Hij', ' ', 1, 1),
               INSTR('ABC def Hij', ' ', 1, 2) - INSTR('ABC def Hij', ' ', 1, 1))),
       soundex(substr('ABC def Hij', INSTR('ABC def Hij', ' ', 1, 2))) -- 第3個單字
from dual

得到：A120 D100 H200，如此即可彌補SOUNDEX只抓前5個字之不足，進行完整字串的相似英文名稱判斷。

Reference

TechOnTheNet.com. Oracle / PLSQL:

1. REGEXP_REPLACE Function
2. REPLACE Function
3. SOUNDEX Function
4. SUBSTR Function
5. INSTR Function