Using regular expressions in Oracle SQL queries, pt. II
Submitted by Tomasz Paprocki on 21 October, 2010 - 21:48
This is a second part of an article on regular expressions usage in Oracle. This part contains SQL examples only, for more details about functions and their parameters see first part of this article.
Regular expressions used in examples are very simple as this is no publication about regular expressions itself.
Using regular expressions in Oracle on actual tables
Let's say we have this simple table (users):
id | login | name | city | |
---|---|---|---|---|
1 | jd | John Doe | Washington | j.doe@abcxyz.com |
2 | ivan | Ivan Ivanovich | Moscow | ivan@qwerty.ru |
3 | jane84 | Jane Doe | Washington | jane@abcxyz.com |
4 | jn2501 | Juan Nadie | Madrit | juan@nadie.biz |
REGEXP_LIKE(source, regexp, modes*)
SELECT name FROM users WHERE REGEXP_LIKE( city , 'M.+' );
Result:
name ---- Ivan Ivanovich Juan Nadie
REGEXP_SUBSTR(source, regexp, position*, occurrence*, modes*)
SELECT DISTINCT REGEXP_SUBSTR ( email , '@([a-z]+)' ) FROM users;
Result:
@abcxyz @qwerty @nadie
REGEXP_REPLACE(source, regexp, replacement*, position*, occurrence*, modes*)
SELECT REGEXP_REPLACE ( email , '@' , ' at ' ) FROM users;
j.doe at abcxyz.com ivan at qwerty.ru jane at abcxyz.com juan at nadie.biz
REGEXP_INSTR(source, regexp, position*, occurrence*, return_option*, modes*)
SELECT id, login, name, city, email FROM users WHERE REGEXP_INSTR ( email , '.' ) < REGEXP_INSTR ( email , '@' );
Result:
id login name city email -- ----- -------- ---------- ---------------- 1 jd John Doe Washington j.doe@abcxyz.com
REGEXP_COUNT(source, regexp, position*, mode*)
SELECT login, REGEXP_COUNT( login , '[0-9]' ) AS num_count FROM users;
Result:
login num_count ----- ---- jd 0 ivan 0 jane84 2 jn2501 4
Comments
Post new comment