How to check a string format with the OWA_PATTERN.MATCH() function
January 20th, 2006 By Francois Degrelle
Assume that you need to check the particular format of a string, e.g. a formated phone number like xx-xx-xx-xx-xx (five 2-digit numbers separated with a minus).
Since the 9i version (or earlier, someone could confirm?) you could use the MATCH() function of the OWA_PATTERN package.
Here is an example:
SQL*Plus: Release 9.0.1.3.0 - Production on Ve Jan 20 10:42:50 2006
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connecté à : Oracle9i Enterprise Edition Release 9.2.0.5.0 -
64bit Production With the Partitioning,
OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> conn tutoforms/tuto@atenadev
Connecté.
SQL> CREATE TABLE phone_number ( num VARCHAR2(15) )
2 /
Table créée.
SQL> INSERT INTO phone_number VALUES( '01.02.03.04.05' )
2 /
1 ligne créée.
SQL> INSERT INTO phone_number VALUES( '01-02-03-04-05' )
2 /
1 ligne créée.
SQL> INSERT INTO phone_number VALUES( '01 02 03 04 05' )
2 /
1 ligne créée.
SQL> select * from phone_number
2 /
NUM
---------------
01.02.03.04.05
01-02-03-04-05
01 02 03 04 05
SQL> set serveroutput on
SQL> BEGIN
2 FOR t IN (SELECT * FROM PHONE_NUMBER)
3 LOOP
4 IF owa_pattern.match(t.num, '^\\d{2}-\\d{2}-\\d{2}-\\d{2}-\\d{2}$') THEN
5 Dbms_Output.Put_Line('Correct format -> ' || t.num);
6 END IF;
7 END LOOP;
8 END;
9 /
Correct format -> 01-02-03-04-05
Procédure PL/SQL terminée avec succès.
Caution! The MATCH() function returns a boolean, so it cannot be used directly in a SQL order.
If you want to read more about the MATCH() function, jump to the official documentation.
January 20th, 2006 at 10:34 am
Very useful Francois, Thanks.
I have tested the above on Oracle DB version 8.1.7 and it worked.
Here is some more information from the OWA_PATTERN package specification:
Assertions:
^ Matches the beginning of a line (or string)
$ Matches the end of a line (or string)
Quantifiers:
{n,m} Must match at least n times, but not more than m times
{n,} Must match at least n times
{n} Must match exactly n times.
* 0 or more occurances
+ 1 or more occurances
? 0 or 1 occurance(s)
Legal atoms:
. matches any character except \n
A list of characters in square brackets [] is a class of characters,
for example [0-9] indicates match any character from 0 to 9.
\n matches newlines
\t matches tabs
\d matches digits [0-9]
\D matches non-digits [^0-9]
\w matches word characters (alphanumeric) [0-9a-z_A-Z]
\W matches non-word characters [^0-9a-z_A-Z]
\s matches whitespace characters [ \t\n]
\S matches non-whitespace characters [^ \t\n]
\b matches on “word” boundaries (between \w and \W)
A backslashed x followed by two hexadecimal digits, such as \x7f,
matches the character having that hexadecimal value.
A backslashed 2 or 3 digit octal number such as \033 matches the
character with the specified value.
Any other “backslashed” character matches itself.
January 23rd, 2006 at 12:27 pm
A link to a great Casimir Saternos Regular expressions article