Using regular expressions in Oracle SQL queries, pt. II

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 email
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.+' );
Returns names of users living in cities with name starting on 'M'.

Result:

name
----
Ivan Ivanovich
Juan Nadie



REGEXP_SUBSTR(source, regexp, position*, occurrence*, modes*)

SELECT
  DISTINCT REGEXP_SUBSTR ( email , '@([a-z]+)' ) 
FROM 
  users;
We want to get statistic of domains used in users email accounts. Region does not matter to us.

Result:

@abcxyz
@qwerty
@nadie



REGEXP_REPLACE(source, regexp, replacement*, position*, occurrence*, modes*)

SELECT
  REGEXP_REPLACE ( email , '@' , ' at ' ) 
FROM
  users;
Replaces all '@' occurences in email field to ' at ' so as result table 'users' will now contain in email field:

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 , '@' );
Returns user row where email prefix contains '.' (dot).

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;
Counts how many numeric characters are used in each login field value.

Result:

login	num_count
-----		----
jd		0
ivan		0
jane84	2
jn2501	4