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:
where ename like '_L%'
or ename like '_D%'
or ename like '_S%';
But with REGEXP_LIKE it's like this:
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 ';' :
Count the number of appearances in a string
select regexp_count(message,'searchvalue') counter
Use in check constraints
Telephone-nr should be in format 999-9999999 :
alter table telephone add constraint tfn_ck1
Unix permissions should be something like 'rwxrw----':
alter table file_storage add constraint tfn_ck2