目的
只保留繁體中文和英文的方法如下(底線需另外處理):
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陳JinAbZ%123','[^a-zA-Za-zA-Z]', '') from dual
會得到:CHENCHIaJinAbZ。(此處連全形英文都保留)
進階應用
搭配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 az AZ 符$#@','[^a-zA-Za-zA-Z ]', '') ,' ',' ') from dual
此處可用TO_SINGLE_BYTE()先將全形轉半形,就只需比對半形英文,結果得到:「az AZ 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: