regular for Oracle 9i
資料來源: phil-sqltips
用途: Oracle DB 9i 之下沒有 regular function, 改用其他方式進行
注意: 畢竟不是原生 function, 執行時效能效能並沒有非常的好, 特別像是 mail list 逐一 check 時會等到昏倒
程式碼:
CREATE OR REPLACE FUNCTION regexp_replace ( source_char IN VARCHAR2 , pattern IN VARCHAR2 , replace_string IN VARCHAR2 , POSITION IN PLS_INTEGER DEFAULT 1 , occurrence IN PLS_INTEGER DEFAULT 0 , match_parameter IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 AS /************************************************************************* * Program : regexp_replace * Version : 1.0 * Author : Philip Moore * Date : 20-JUN-2009 Anno Domini * Purpopse : This provides a pseudo "REGEXP_REPLACE" function for Oracle 9iR2 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)! *************************************************************************/ -- Variables l_source_string VARCHAR2 (32767); l_temp_string VARCHAR2 (32767); l_flags VARCHAR2 (10); l_occurrence PLS_INTEGER; l_end_of_pattern_pos PLS_INTEGER; l_string_pos PLS_INTEGER; BEGIN -- Substr the source_char to start at the position specified l_source_string := SUBSTR (source_char, POSITION); -- Set up the flags argument IF occurrence = 0 THEN l_flags := 'g'; ELSE l_flags := NULL; END IF; l_flags := l_flags || match_parameter; -- Now replace the regular expression pattern globally if "g" IF INSTR (l_flags, 'g') > 0 THEN owa_pattern.CHANGE (line => l_source_string , from_str => pattern , to_str => replace_string , flags => l_flags); -- Not a global replace - loop until the "occurrence"th occurrence is replaced... ELSE l_string_pos := 0; l_occurrence := 0; WHILE l_string_pos < LENGTH (l_source_string) AND l_occurrence < occurrence LOOP l_string_pos := l_string_pos + 1; l_end_of_pattern_pos := owa_pattern.amatch (line => l_source_string , from_loc => l_string_pos , pat => pattern , flags => match_parameter); IF l_end_of_pattern_pos != 0 THEN l_occurrence := l_occurrence + 1; END IF; END LOOP; IF l_occurrence = occurrence THEN l_temp_string := SUBSTR (l_source_string , l_string_pos , (l_end_of_pattern_pos - l_string_pos)); owa_pattern.CHANGE (line => l_temp_string , from_str => pattern , to_str => replace_string , flags => l_flags); l_source_string := SUBSTR (l_source_string , 1 , l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos); END IF; END IF; -- Piece the string back together if needed... IF POSITION > 1 THEN l_source_string := SUBSTR (source_char , 1 , (POSITION - 1)) || l_source_string; END IF; RETURN l_source_string; END regexp_replace; CREATE OR REPLACE FUNCTION regexp_like (source_char IN VARCHAR2 , pattern IN VARCHAR2 , match_parameter IN VARCHAR2 DEFAULT NULL) RETURN INTEGER AS /************************************************************************* * Program : regexp_like * Version : 1.0 * Author : Philip Moore * Date : 20-JUN-2009 Anno Domini * Purpopse : This provides a pseudo "REGEXP_LIKE" operator for Oracle 9iR2 * Warnings : Do NOT compile in an Oracle 10GR2 database (or above)! *************************************************************************/ -- Variables l_return INTEGER; BEGIN IF owa_pattern.match (line => source_char , pat => pattern , flags => match_parameter) THEN l_return := 1; ELSE l_return := 0; END IF; RETURN l_return; END regexp_like;
使用範例1:
SELECT regexp_replace ('5138675309', '(d{3})(d{3})(d{4})', '(1) 2-3') AS phone FROM v$version WHERE banner = 'Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production';
執行結果:
PHONE
—————-
(513) 867-5309
使用範例2 (Email Address驗證):
BEGIN FOR ii IN 1 .. v_cnt LOOP --找不到','mail 位置只有一個就取全部的長度 SELECT DECODE(INSTRB(v_email_list, ',', v_fm_pos), 0, LENGTHB(v_email_list), INSTRB(v_email_list, ',', v_fm_pos) - 1) INTO v_to_pos FROM dual; v_len := v_to_pos - v_fm_pos + 1; v_email := SUBSTRB(v_email_list, v_fm_pos, ABS(v_len)); IF regexp_like(v_email, '^w{1,}[.,0-9,a-z,A-Z,_]w{1,}[.,0-9,a-z,A-Z,_]w{1,}' || '@w{1,}[.,0-9,a-z,A-Z,_]w{1,}[.,0-9,a-z,A-Z,_]w{1,}[.,0-9,a-z,A-Z,_]w{1,}$', NULL) THEN NULL; -- error END IF; v_fm_pos := v_to_pos + 2; END LOOP; END;