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.
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 |
SELECT name FROM users WHERE REGEXP_LIKE( city , 'M.+' );
Result:
name ---- Ivan Ivanovich Juan Nadie
SELECT DISTINCT REGEXP_SUBSTR ( email , '@([a-z]+)' ) FROM users;
Result:
@abcxyz @qwerty @nadie
SELECT REGEXP_REPLACE ( email , '@' , ' at ' ) FROM users;
j.doe at abcxyz.com ivan at qwerty.ru jane at abcxyz.com juan at nadie.biz
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
SELECT login, REGEXP_COUNT( login , '[0-9]' ) AS num_count FROM users;
Result:
login num_count ----- ---- jd 0 ivan 0 jane84 2 jn2501 4