-- Test for the regexp_*() function \set ECHO none SET search_path TO oracle,"$user", public, pg_catalog; ---- -- Tests for REGEXP_LIKE() ---- -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('a'||CHR(10)||'d', 'a.d'); -> NULL SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'm'); -> NULL SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'm'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'n'); -> 1 SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'n'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('Steven', '^Ste(v|ph)en$'); -> 1 SELECT REGEXP_LIKE('Steven', '^Ste(v|ph)en$'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE regexp_like('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar'); -> NULL SELECT REGEXP_LIKE('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE regexp_like('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', 'bar'); -> 1 SELECT REGEXP_LIKE('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', 'bar'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE regexp_like('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 'm'); -> 1 SELECT REGEXP_LIKE('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 'm'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE regexp_like('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 'n'); -> NULL SELECT REGEXP_LIKE('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 'n'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('GREEN', '([aeiou])\1'); -> NULL SELECT REGEXP_LIKE('GREEN', '([aeiou])\1'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('GREEN', '([aeiou])\1', 'i'); -> 1 SELECT REGEXP_LIKE('GREEN', '([aeiou])\1', 'i'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 'i'); -> 1 SELECT REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 'i'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 'i'); -> NULL SELECT REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 'i'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 'in'); -> 1 SELECT REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 'in'); regexp_like ------------- t (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 'in'); -> NULL SELECT REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 'in'); regexp_like ------------- f (1 row) -- ORACLE> SELECT 1 FROM DUAL WHERE REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 'im'); -> 1 SELECT REGEXP_LIKE('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 'im'); regexp_like ------------- t (1 row) ---- -- Tests for REGEXP_COUNT() ---- -- ORACLE> SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d') FROM DUAL; -> 0 SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'm') FROM DUAL; -> 0 SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'm'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'n') FROM DUAL; -> 1 SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'n'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('Steven', '^Ste(v|ph)en$') FROM DUAL; -> 1 SELECT REGEXP_COUNT('Steven', '^Ste(v|ph)en$'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar') FROM DUAL; -> 0 SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', 'bar') FROM DUAL; -> 1 SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', 'bar'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 0, 'm') FROM DUAL; -> ORA-01428: argument '0' is out of range SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 0, 'm'); ERROR: argument 'position' must be a number greater than 0 -- ORACLE> SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 1, 'm') FROM DUAL; -> 1 SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 1, 'm'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 1, 'n') FROM DUAL; -> 0 SELECT REGEXP_COUNT('foo' || chr(10) || 'bar' || chr(10) || 'bequq' || chr(10) || 'baz', '^bar', 1, 'n'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('GREEN', '([aeiou])\1') FROM DUAL; -> 0 SELECT REGEXP_COUNT('GREEN', '([aeiou])\1'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('GREEN', '([aeiou])\1', 1, 'i') FROM DUAL; -> 1 SELECT REGEXP_COUNT('GREEN', '([aeiou])\1', 1, 'i'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 1, 'i') FROM DUAL; -> 1 SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 1, 'i'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 1, 'i') FROM DUAL; -> 0 SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 1, 'i'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 1, 'in') FROM DUAL; -> 1 SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '([aeiou])\1', 1, 'in'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 1, 'in') FROM DUAL; -> 0 SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 1, 'in'); regexp_count -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 1, 'im') FROM DUAL; -> 1 SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', 1, 'im'); regexp_count -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT FROM DUAL; -> 5 SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i'); regexp_count -------------- 5 (1 row) -- ORACLE> SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL; -> 3 SELECT REGEXP_COUNT('123123123123', '123', 3, 'i'); regexp_count -------------- 3 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ABC123', '[A-Z]'), REGEXP_COUNT('A1B2C3', '[A-Z]') FROM DUAL; -> 3 | 3 SELECT REGEXP_COUNT('ABC123', '[A-Z]'), oracle.REGEXP_COUNT('A1B2C3', '[A-Z]'); regexp_count | regexp_count --------------+-------------- 3 | 3 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ABC123', '[A-Z][0-9]'), REGEXP_COUNT('A1B2C3', '[A-Z][0-9]') FROM DUAL; -> 1 | 3 SELECT REGEXP_COUNT('ABC123', '[A-Z][0-9]'), oracle.REGEXP_COUNT('A1B2C3', '[A-Z][0-9]'); regexp_count | regexp_count --------------+-------------- 1 | 3 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ABC123', '^[A-Z][0-9]'), REGEXP_COUNT('A1B2C3', '^[A-Z][0-9]') FROM DUAL; -> 0 | 1 SELECT REGEXP_COUNT('ABC123', '^[A-Z][0-9]'), oracle.REGEXP_COUNT('A1B2C3', '^[A-Z][0-9]'); regexp_count | regexp_count --------------+-------------- 0 | 1 (1 row) -- ORACLE> SELECT REGEXP_COUNT('ABC123', '([A-Z][0-9]){2}'), REGEXP_COUNT('A1B2C3', '([A-Z][0-9]){2}') FROM DUAL; -> 0 | 1 SELECT REGEXP_COUNT('ABC123', '([A-Z][0-9]){2}'), oracle.REGEXP_COUNT('A1B2C3', '([A-Z][0-9]){2}'); regexp_count | regexp_count --------------+-------------- 0 | 1 (1 row) -- Check negatives values that must throw an error SELECT REGEXP_COUNT('ORANGE' || chr(10) || 'GREEN', '^..([aeiou])\1', -1, 'i'); ERROR: argument 'position' must be a number greater than 0 ---- -- Tests for REGEXP_INSTR() ---- -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))') FROM DUAL; -> 1 SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))'); regexp_instr -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(4(56)(78))') FROM DUAL; -> 4 SELECT REGEXP_INSTR('1234567890', '(4(56)(78))'); regexp_instr -------------- 4 (1 row) -- ORACLE> SELECT regexp_instr('1234567890', '123(4(56)(78))', 3) FROM DUAL; -> 0 SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 3); regexp_instr -------------- 0 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(4(56)(78))', 3) FROM DUAL; -> 4 SELECT REGEXP_INSTR('1234567890', '(4(56)(78))', 3); regexp_instr -------------- 4 (1 row) -- ORACLE> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6) FROM DUAL; -> 37 SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[^ ]+', 1, 6); regexp_instr -------------- 37 (1 row) -- ORACLE> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 0) FROM DUAL; -> 21 SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 0); regexp_instr -------------- 21 (1 row) -- ORACLE> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 1) FROM DUAL; -> 28 SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 1); regexp_instr -------------- 28 (1 row) -- ORACLE> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p][[:alpha:]]{6}', 3, 2, 1, 'i') FROM DUAL; -> 28 SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[q|r|p][[:alpha:]]{6}', 3, 2, 1, 'i'); regexp_instr -------------- 28 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 0) FROM DUAL; -> 1 SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 0); regexp_instr -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1) FROM DUAL; -> 1 SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 1); regexp_instr -------------- 1 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) FROM DUAL; -> 4 SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2); regexp_instr -------------- 4 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) FROM DUAL; -> 7 SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4); regexp_instr -------------- 7 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) FROM DUAL; -> 7 SELECT REGEXP_INSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4); regexp_instr -------------- 7 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 2, 0, 'i', 4) FROM DUAL; -> 18 SELECT REGEXP_INSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 2, 0, 'i', 4); regexp_instr -------------- 18 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 2, 1, 'i', 4) FROM DUAL; -> 20 SELECT REGEXP_INSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 2, 1, 'i', 4); regexp_instr -------------- 20 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, 3, 0,'i', 4) FROM DUAL; -> 29 SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, 3, 0, 'i', 4); regexp_instr -------------- 29 (1 row) -- ORACLE> SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, 3, 1,'i', 4) FROM DUAL; -> 31 SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, 3, 1, 'i', 4); regexp_instr -------------- 31 (1 row) -- DROP TABLE regexp_temp; -- CREATE TABLE regexp_temp(empName varchar2(20), emailID varchar2(20)); -- INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com'); -- INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe'); -- COMMIT; CREATE TEMPORARY TABLE regexp_temp(empName varchar(20), emailID varchar(20)); INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com'); INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', 'janedoe'); -- -- ORACLE> SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+') "IS_A_VALID_EMAIL" FROM regexp_temp; -- EMAILID IS_A_VALID_EMAIL -- -------------------- ---------------- -- johndoe@example.com 1 -- example.com 0 SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+') FROM regexp_temp; emailid | regexp_instr ---------------------+-------------- johndoe@example.com | 1 janedoe | 0 (2 rows) -- -- ORACLE> SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+', 1, 1, 0, 'i', 0) "IS_A_VALID_EMAIL" FROM regexp_temp; -- EMAILID IS_A_VALID_EMAIL -- -------------------- ---------------- -- johndoe@example.com 1 -- example.com 0 SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+', 1, 1, 0, 'i', 0) FROM regexp_temp; emailid | regexp_instr ---------------------+-------------- johndoe@example.com | 1 janedoe | 0 (2 rows) -- -- ORACLE> SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+', 1, 1, 0, 'i', 1) "IS_A_VALID_EMAIL" FROM regexp_temp; -- EMAILID IS_A_VALID_EMAIL -- -------------------- ---------------- -- johndoe@example.com 16 -- example.com 0 SELECT emailID, REGEXP_INSTR(emailID, '\w+@\w+(\.\w+)+', 1, 1, 0, 'i', 1) FROM regexp_temp; emailid | regexp_instr ---------------------+-------------- johndoe@example.com | 16 janedoe | 0 (2 rows) DROP TABLE regexp_temp; -- Check negatives values that must throw an error SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', -1, 3, 1, 'i', 4); ERROR: argument 'position' must be a number greater than 0 SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, -3, 1, 'i', 4); ERROR: argument 'occurence' must be a number greater than 0 SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, 3, -1, 'i', 4); ERROR: argument 'return_opt' must be 0 or 1 SELECT REGEXP_INSTR('1234567890 1234567890 1234567890', '(123)(4(56)(78))', 1, 3, 1, 'i', -4); ERROR: argument 'group' must be a positive number -- ORACLE> SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6) FROM DUAL; -> 37 SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6) ; regexp_instr -------------- 37 (1 row) -- ORACLE> SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6, 1) FROM DUAL; -> 39 SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6, 1) ; regexp_instr -------------- 39 (1 row) -- ORACLE> SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6, 1, 'i') FROM DUAL; -> 39 SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6, 1, 'i') ; regexp_instr -------------- 39 (1 row) ---- -- Tests for REGEXP_SUBSTR() ---- -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+') FROM DUAL; -> , zipcode town SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+'); regexp_substr ---------------- , zipcode town (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') FROM DUAL; -> http://www.example.com/ SELECT REGEXP_SUBSTR('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?'); regexp_substr ------------------------- http://www.example.com/ (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+', 24) FROM DUAL; -> , FR SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+', 24); regexp_substr --------------- , FR (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+', 1, 1) FROM DUAL; -> , zipcode town SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+', 1, 1); regexp_substr ---------------- , zipcode town (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+', 1, 2) FROM DUAL; -> , FR SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+', 1, 2); regexp_substr --------------- , FR (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1) FROM DUAL; -> NULL SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1); regexp_substr --------------- (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1, 'i') FROM DUAL; -> , zipcode town SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1, 'i'); regexp_substr ---------------- , zipcode town (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1, 'i', 0) FROM DUAL; -> , zipcode town SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1, 'i', 0); regexp_substr ---------------- , zipcode town (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1, 'i', 1) FROM DUAL; -> NULL SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+[Zf][^,]+', 1, 1, 'i', 1); regexp_substr --------------- (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+([Zf][^,]+)', 1, 1, 'i', 1) FROM DUAL; -> zipcode town SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',\s+([Zf][^,]+)', 1, 1, 'i', 1); regexp_substr --------------- zipcode town (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4) FROM DUAL; -> 78 SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 4); regexp_substr --------------- 78 (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('1234567890 1234557890', '(123)(4(5[56])(78))', 1, 2, 'i', 3) FROM DUAL; -> 55 SELECT REGEXP_SUBSTR('1234567890 1234557890', '(123)(4(5[56])(78))', 1, 2, 'i', 3); regexp_substr --------------- 55 (1 row) -- ORACLE> SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) FROM DUAL; -> 12345678 SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0); regexp_substr --------------- 12345678 (1 row) -- Check negatives values that must throw an error SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', -1, 1, 'i', 0); ERROR: argument 'position' must be a number greater than 0 SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, -1, 'i', 0); ERROR: argument 'occurence' must be a number greater than 0 SELECT REGEXP_SUBSTR('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', -1); ERROR: argument 'group' must be a positive number ---- -- Tests for REGEXP_REPLACE() ---- -- ORACLE> SELECT REGEXP_REPLACE('512.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') FROM DUAL; -> (512) 123-4567 SELECT REGEXP_REPLACE('512.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3'); regexp_replace ---------------- (512) 123-4567 (1 row) -- ORACLE> SELECT REGEXP_REPLACE('512.123.4567 612.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') FROM DUAL; -> (512) 123-4567 (612) 123-4567 SELECT oracle.REGEXP_REPLACE('512.123.4567 612.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3'); regexp_replace ------------------------------- (512) 123-4567 (612) 123-4567 (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ') FROM DUAL; -> number your street, zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' '); regexp_replace -------------------------------------- number your street, zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street,'||CHR(10)||' zipcode town, FR', '( ){2,}', ' ') FROM DUAL; -> number your street, -- zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street,'||CHR(10)||' zipcode town, FR', '( ){2,}', ' '); regexp_replace --------------------- number your street,+ zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9) FROM DUAL; -> number your street, zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9); regexp_replace ---------------------------------------- number your street, zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 0) FROM DUAL; -> number your street, zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 0); regexp_replace ---------------------------------------- number your street, zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2) FROM DUAL; -> number your street, zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2); regexp_replace --------------------------------------------- number your street, zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2, 'm') FROM DUAL; -> number your street, zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2, 'm'); regexp_replace --------------------------------------------- number your street, zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '([EURT]){2,}', '[\1]', 9, 2, 'i') FROM DUAL; -> number your s[t], zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '([EURT]){2,}', '[\1]', 9, 2, 'i'); regexp_replace ---------------------------------------------- number your s[t], zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE('number your street, zipcode town, FR', '[ ]{2,}', ' ', 9, 2) FROM DUAL; -> number your street, zipcode town, FR SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2); regexp_replace --------------------------------------------- number your street, zipcode town, FR (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'A|e|i|o|u', 'X', 1, 2) FROM DUAL; -> A PXstgreSQL function SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'A|e|i|o|u', 'X', 1, 2); regexp_replace ----------------------- A PXstgreSQL function (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i') FROM DUAL; -> X PXstgrXSQL fXnctXXn SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 0, 'i'); regexp_replace ----------------------- X PXstgrXSQL fXnctXXn (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'i') FROM DUAL; -> X PostgreSQL function SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'i'); regexp_replace ----------------------- X PostgreSQL function (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 2, 'i') FROM DUAL; -> A PXstgreSQL function SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 2, 'i'); regexp_replace ----------------------- A PXstgreSQL function (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i') FROM DUAL; -> A PostgrXSQL function SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 3, 'i'); regexp_replace ----------------------- A PostgrXSQL function (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 9, 'i') FROM DUAL; -> A PostgreSQL function SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 9, 'i'); regexp_replace ----------------------- A PostgreSQL function (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'A|e|i|o|u', 'X', 1, 9) FROM DUAL; -> A PostgreSQL function SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'A|e|i|o|u', 'X', 1, 9); regexp_replace ----------------------- A PostgreSQL function (1 row) -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', -1, 0, 'i') FROM DUAL; -> ORA-01428 SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', -1, 0, 'i'); ERROR: argument 'position' must be a number greater than 0 -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, -1, 'i') FROM DUAL; -> ORA-01428 SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, -1, 'i'); ERROR: argument 'occurrence' must be a positive number -- ORACLE> SELECT REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'g') FROM DUAL; -> ORA-01760 SELECT oracle.REGEXP_REPLACE ('A PostgreSQL function', 'a|e|i|o|u', 'X', 1, 1, 'g'); ERROR: argument 'flags' has unsupported modifier(s). -- -- Test NULL input in the regexp_* functions that must returned NULL except for the modifier -- or regexp flag. There is an exception with regexp_replace(), if the pattern is null (second -- parameter) the original string is returned. We don't test functions witht the STRICT attribute -- SELECT oracle.REGEXP_LIKE(NULL, '\d+', 'i'); regexp_like ------------- (1 row) SELECT oracle.REGEXP_LIKE('1234', NULL, 'i'); regexp_like ------------- (1 row) SELECT oracle.REGEXP_LIKE('1234', '\d+', NULL); regexp_like ------------- t (1 row) SELECT oracle.REGEXP_LIKE('1234', '\d+', ''); regexp_like ------------- t (1 row) SELECT oracle.REGEXP_COUNT('1234', '\d', NULL) ; regexp_count -------------- (1 row) SELECT oracle.REGEXP_COUNT('1234', '\d', 1, NULL) ; regexp_count -------------- 4 (1 row) SELECT oracle.REGEXP_COUNT('1234', '\d', 1, '') ; regexp_count -------------- 4 (1 row) SELECT oracle.REGEXP_COUNT('1234', '\d', NULL, NULL) ; regexp_count -------------- (1 row) SELECT oracle.REGEXP_COUNT(NULL, '4', 1, 'i'); regexp_count -------------- (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', NULL); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, NULL); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, 1, NULL); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, 1, 1, NULL); regexp_instr -------------- 5 (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, 1, 1, NULL, 0); regexp_instr -------------- 5 (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, 1, 0, NULL); regexp_instr -------------- 4 (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, 1, 0, 'i', NULL); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_INSTR('1234', '4', 1, 1, 0, '', NULL); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_INSTR(NULL, '4', 1, 1, 0, 'i', 2); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_INSTR(NULL, '4', 1, 1, 0, 'i', 2); regexp_instr -------------- (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '1(.*)', null); regexp_substr --------------- (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '234', 1, null); regexp_substr --------------- (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '234', 1, 1, null); regexp_substr --------------- 234 (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '234', 1, 1, ''); regexp_substr --------------- 234 (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '234', 1, 1, 'i', null); regexp_substr --------------- (1 row) -- test for capture group SELECT oracle.REGEXP_SUBSTR('1234', '2(3)(4)', 1, 1, 'i', 1); regexp_substr --------------- 3 (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '2(3)(4)', 1, 1, 'i', 2); regexp_substr --------------- 4 (1 row) SELECT oracle.REGEXP_SUBSTR('1234', '2(3)(4)', 1, 1, 'i', 0); regexp_substr --------------- 234 (1 row) -- Special case for second parameter in REGEXP_REPLACE, when null returns the original value. SELECT oracle.REGEXP_REPLACE(null, '\d', 'a'); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, 'a'); regexp_replace ---------------- 1234 (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, null); regexp_replace ---------------- 1234 (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', 'a', null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, 'a', 2); regexp_replace ---------------- 1234 (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, 'a', null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, 'a', 1); regexp_replace ---------------- 1234 (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, 'a', 1, null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', 'a', 1, null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', 'a', 1, null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', 'a', 1, null); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', 'a', 1, 1, null); regexp_replace ---------------- a234 (1 row) SELECT oracle.REGEXP_REPLACE('1234', '\d', 'a', 1, NULL, 'i'); regexp_replace ---------------- (1 row) SELECT oracle.REGEXP_REPLACE('1234', null, 'a', 1, 1, 'i'); regexp_replace ---------------- 1234 (1 row)