p33d 
 
This has nothing to do with triggers.
 
Oracle now has regular expressions.
 
Check them out here:
 
http://www.regular-expressions.info/oracle.html
AND
http://www.psoug.org/reference/regexp.html
 
This is a BIG topic.
 
A regular expression is a kind of formula that lets you search
for every string that "looks like the formula."
 
UNIX, vi, Oracle, even Microsoft Word has regular expressions built
in. There are however small differences. The best implementation
(in my opinion) is in vi. However, this is a database class, so we
will concentrate on database queries.
 
Let's start simple. You have a database table "companies"
with a single column "name" that contains:
 
Permasteel
Allsteel
USrubber
 
You want to find all steel companies. Here is how.
 
select * from companies where regexp_like(name, 'steel')
/
 
That is the basic function. It's not a regular expression yet.
 
-----------------------------------------------------
[a-z] is a letter range.
 
That means, find all strings that contains ONE SINGLE CHARACTER
that is either an 'a' or a 'b' or a 'c' or a .... 'z'.
 
select * from companies where regexp_like(name, '[a-z]')
/
 
This will find Permasteel Allsteel and USrubber,
because they all contain FOR EXAMPLE one "e" which is 
a character between "a" and "z".
(Remember the ASCII table? That's how Oracle knows
what is between "a" and "z".)
 
But if we do:
 
select * from companies where regexp_like(name, '[A]')
/
 
It will only find 
Allsteel
 
Neither USrubber nor Permasteel contains a capital letter "A".
 
select * from companies where regexp_like(name, '[P]')
/
 
Will find 
Permasteel
 
select * from companies where regexp_like(name, '[A-P]')
/
 
will find 
Allsteel
Permasteel
 
And
 
select * from companies where regexp_like(name, '[AP]')
/
 
 
will also find both
Allsteel
Permasteel
 
---------------------------------
 
Let's do something more sophisticated.
We have a lot of numbers in one column,
including $amounts, phone numbers and zip codes
and we want to find all phone numbers.
 
Note that we are storing all numbers as varchar2 !!!
 
I will do "select * from phone" to show you what is
already in the table "phone" with the column "phonenum".
 
So the simplest phone number would be something like
 
973-123-4567
 
More abstractly, this can be written as:
 
ddd-ddd-dddd 
 
where d is any digit and - is actually -.
 
Let's find all such phone numbers in the table.
 
Now we need a range [0-9].
 
select * from phone where regexp_like(phonenum, 
'[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
/
 
But some phone numbers look like this:
 
(ddd)ddd-dddd
 
So what do we do to select those?
We could try this.
 
select * from phone where regexp_like(phonenum, 
'([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
/
 
But it does not work!!!
Why? Because ( ) HAVE A SPECIAL MEANING in
regular expressions. 
 
They are "meta-characters".
They "don't mean what they normally mean, they
mean something else specific to the regular 
expression language."
 
Now what?
 
Most computer languages have something called
ESCAPE CHARACTERS that change the normal meaning
of a symbol.
 
In UNIX, vi, etc. the most common escape character
is the backslash.
 
This works here too.
 
So \( means: Don't use the REGULAR EXPRESSION
MEANING of (
 
Use the "other" normal meaning.
 
Same thing for \) of course.
 
So, the following works.
 
select * from phone where regexp_like(phonenum, 
'\([0-9][0-9][0-9]\)[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
/
 
So this finds
 
(973)123-4567 and anything similar.
 
Note that I haven't told you yet what ( ) in a regular 
expression means, and I will not tell you for a while.
-------------------------------------------
 
A single dot matches any character.
.
 
So if you are looking for an actual dot (.)
like for example a number like $45.60
then you need to escape the dot as
\.
 
select * from phone where regexp_like(phonenum, '[0-9]\.[0-9]')
/
 
-------------------------------------------
 
Look back at the table.
There is another phone number that seems to look like
 
(ddd)ddd-dddd
 
But the above select statement did not find it.
Why?
 
Because actually that phone number has one blank
after the )
 
(ddd) ddd-dddd 
 
And another phone number has accidentally TWO
blanks after the ).
 
How do we find this?
We could put a blank in to the regular expression.
But then we will not find
 
(ddd)ddd-dddd
 
and also not
 
(ddd) ddd-dddd /* two blanks */
 
anymore!!!
 
Solution:
 
select * from phone where regexp_like(phonenum, 
'\([0-9][0-9][0-9]\)[ ]*[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
/
 
This is your first "really interesting regular expression."
 
[ ] means the range that contains exactly one BLANK.
Compare to [] which contains nothing.
 
And * is a meta-character that means:
 
Do it 0 times 
or do it 1 time
or do it 2 times
or do it 3 times
or do it...
 
Therefore
[ ]* means, if there is nothing, find it. 
If there is one blank, find it.
If there are many blanks, find them.
 
So the above select statement will find all 3 phone numbers.
 
(973)123-4567
(973) 123-4567
(973) 123-4567
 
But it will not find the
 
ddd-ddd-dddd
 
number!
 
Well, we can use an Oracle solution for this.
Instead of a regular expression solution.
 
 
select * from phone where regexp_like(phonenum, 
'\([0-9][0-9][0-9]\)[ ]*[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
 or regexp_like(phonenum, 
'[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]');
/
 
This is pretty good now.
 
But it still does not cover numbers like
 
123-4567890
 
You already learned one solution for this.
You could solve this by putting
[-]*
between
 
ddd-ddd and dddd
 
but that is not a good solution, because there
should never be more than one –
We’ll return to this problem below.
 
------------------
Now let's add one more company:
 
STEELisUS
 
select * from companies where regexp_like(name, 'steel')
/
 
Would not find it.
 
Of course we could look for 'STEEL' and 'steel' and 'Steel'
but that is pretty inconvenient. 
 
Instead we can "ignore case": 'i' as third argument
 
select * from companies where regexp_like(name, 'steel', 'i')
/
 
Now let's say we want to find all steel companies, but
only if the name STARTS with 'steel'.
 
The metacharacter ^ means "beginning of line"
or for Oracle "beginning of string."
 
select * from companies where regexp_like(name, '^steel', 'i')
/
 
STEELisUS
 
And what if we want only the companies that have names
that end in "steel"?
 
The metacharacter $ means "end of line."
 
select * from companies where regexp_like(name, 'steel$', 'i')
/
 
Allsteel
Permasteel
 
 
===========================================
Let's get more sophisticated.
 
* means "0 or more repetitions". We had this already.
+ means "1 or more repetitions". 
? means "0 or 1 repetitions". That is also called "optional". 
 
Before this worked for:
 
select * from phone where regexp_like(phonenum,
'\([0-9][0-9][0-9]\)[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
/
 
(ddd)ddd-dddd
 
But it would not find 
 
(ddd)ddddddd
 
So now we make the - optional, with a ?
 
select * from phone where regexp_like(phonenum,
'\([0-9][0-9][0-9]\)[0-9][0-9][0-9]-?[0-9][0-9][0-9][0-9]')
/
 
Now it finds even 
 
(ddd)ddddddd
 
-------------------------
Let's say we now want to find anything at all,
just it should not contain small letters.
 
 
select * from phone where regexp_like(phonenum,
'[^a-z]')
/
 
This would exclude any string that consists
ONLY of small letters.
 
NOTE THAT HERE ^ MEANS not.
It does not mean BEGINNING.
 
NOTE also that it applies to the whole range a-z.
 
So in more detail it means:
 
Select this if EVERY character is 
Not an ‘a’ and every character is not a ‘b’ and
every character is NOT a ‘c’ and and and
 
 
So if there is a blank between two words,
this would be displayed.
 
I can exclude words with blanks by adding blank
to the excluded characters.
 
select * from phone where regexp_like(phonenum,
'[^a-z ]');
/
 
The ^ means again NOT, and it applies even to
the blank.
 
 
 
=========================
 
 
Can use regexp_like in an UPDATE
in an update statement:
 
 
 
create table test9 (first varchar2(20), last varchar2(20));
/
insert into test9 values ('Joe', 'Smith');
/
insert into test9 values ('Tom', 'Ford');
/
 
select * from test9;
 
update test9
set last = 'Brown'
where regexp_like(last, '[S]');
 
This replaces Smith by Brown.
 
So… one more time … [S] means that it is true there is a 




capital S somewhere in the last name. AND I DON’T CARE




WHAT ALL THE OTHER LETTERS ARE.
==============================================
 
Besides regexp_like there are a few other relevant functions.
 
regexp_like is used in the WHERE clause.
 
The others are used in the SELECT clause:
 
regexp_substr
regexp_replace
regexp_instr
 
Now I finally tell you what the ( ) does.
It is a capturing group.
It REMEMBERS what you matched.
 
Capturing groups are allowed ( ) \1 \2, etc.
 
You would use those in a third argument of regexp_replace.
 
Like this
 
SELECT regexp_replace( name of COLUMN , 'a PATTERN WITH () in it', '\1' )
 
For example:
 
select regexp_replace(phonenum, '\(([0-9][0-9][0-9])\)[
]*[0-9][0-9][0-9]-?[0-9][0-9][0-9][0-9]', '\1') from phone
/
 
This matches phone numbers like
(ddd) ddd-dddd
but when it matches them
it shows the user the three digits in the ( ) THREE TIMES.
 
To make this clearer I'll use c instead of d now:
 
(ccc) ddd-dddd
 
The user will see
 
ccc
 
Why would I want this?
 
I might just want to find all the area codes
that I have in my database.
 
Also note the 
\( ( ) \)
 
The first \( matches a (.
The second ( CAPTURES THE DATA.
 
As so often, this was JUST THE BEGINNING.
There is so much more...