Re: Data validation

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Wed, 14 Jan 2004 10:52:21 -0000
Message-ID: <bu3779$rkk$1@gazette.almaden.ibm.com>

"Fredrik Bertilsson" <fredrik_bertilsson_at_passagen.se> wrote in message news:31f7e57d.0401130523.4d984119_at_posting.google.com...
> I want to have a data validation rule that defines a pattern (in
> regular expression if possible) for phone numbers. Only phone numbers
> that matches this pattern should be possible to insert into the
> database.
>
> The application that reads and updates this column should be able to
> work with different database vendors. So the SQL statements for
> select, update and insert must be identical for different vendors.
>
> Are there any way of doing this (that works with most of the popular
> databases), without using triggers?

Maybe you could compile up an external user defined function on each 'popular' DBMS that wraps a regex function. Not sure if the SQL function call syntax would be the same across the board however.

See here for an DB2 example

http://www-106.ibm.com/developerworks/db2/library/techarticle/0301stolze/0301stolze.html

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Wed Jan 14 2004 - 04:52:21 CST