www.oradev.com
  Database   Sql   Functions   Packages   Performance   Books   Oracle   Other   About   XML   ORA-messages
  Usage of regular Expressions in Oracle SQL

Database/Sql

Oracle Regular Expressions
Timestamp
SQL Date format
String concatenation
Loop in pl/sql
SQL IN-clause
Regular Expressions Examples
Flashback query
Grant/revoke privileges
Sequence
Rename tables, columns
Insert into Oracle
Database name
Table with sequenced numbers
Oracle connect by
Add columns to table


  OraDev.com

Examples on how to use regular Expressions in Oracle SQL

Since Oracle 10g you can use regular expressions in the database. Regular expressions enable you to search for patterns in string data by using standardized syntax conventions. The full syntax is explained here

Alternative for search with like

: The REGEXP_LIKE is more powerfull then the SQL Like command. For example if you search for content where the second character is L,D or S, you can do it with:
select ename
from emp
where ename like '_L%'
or ename like '_D%' 
or ename like '_S%'; 
But with REGEXP_LIKE it's like this:
select ename
from emp
where regexp_like(ename,'^.[LDS]');

Manipulate strings with REGEXP_SUBSTR

: When you have a datafield with multiple lines (so a lot of linefeeds in 1 data-field, you can easily extract the one line you are searching for with REGEXP_SUBSTR.
select  filename, REGEXP_SUBSTR(message, '.*#F1.*', 1,1,'m')) from tab1;

Extract the n'th word in a line with field-separators. Give the second word, where the values are separated with ';' :
select regexp_substr('first;second;third;fourth','[^;]+,1,2) 
from dual;

Count the number of appearances in a string

select regexp_count(message,'searchvalue') counter from source_table;

Use in check constraints

Telephone-nr should be in format 999-9999999 :
alter table telephone add constraint tfn_ck1
CHECK(regexp_like(telephone_nr,'[0-9]{3}-[0-9]{7}'));
Unix permissions should be something like 'rwxrw----':
alter table file_storage add constraint tfn_ck2
CHECK(regexp_like(unix_permissions,'([r-][w-][x-]){3}'));