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;