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}'));
|
