Check constraint - numbers only in varchar2 field 1

[CatMackerel]

Hello,

I'm currently using a VARCHAR2 datatype for a telephone number field so that I can keep leading zeros, and I'd like to add a check constraint to the column so that only numeric characters are entered.

Is this possible, and if so, how?

Thanks in advance

 

[THoey]

I did a search for a thread that I started a while back. The gist of what you need to do is there:

thread186-103913 Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[CatMackerel]

Thanks for your response Terry,

I've now created a function that uses TO_NUMBER and am wondering; is it possible to call a user defined function in a check constraint, or can it only be done with a trigger?

I have one book that states it's possible (but doesn't give any examples) and one that says it's not.

Regards,

Matt

 

[karluk]

Have you tried creating a check constraint with your user defined function? I get errors when I try. It appears that it's not permitted.

On the larger issue of checking a string for numeric characters, I see that there is a package in the 8.1.7 catalog named owa_pattern. It looks as if this package will do this kind of checking. Check the following PL/SQL code

begin
if owa_pattern.match('222-1234','\D+') then
dbms_output.put_line('string in nonnumeric');
else
dbms_output.put_line('string is numeric');
end if;
end;

The '\D+' pattern looks for at least one character other than 0..9, so this check will work if you don't store formatting characters like hyphens in your phone numbers. However owa_pattern is extremely powerful, so you can get it to check for formatting characters too.

 

[THoey]

Cool find Karluk... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[CatMackerel]

Thanks, you've both been very helpful.

And no, it's not possible to call a user defined function from a check constraint. After double checking the book, it only says 'functions can be called'. Guess it means only pre-defined Oracle functions.

Cheers!