Home » Developer & Programmer » Forms » How to Validate The Numbers in the Varchar Data Type Field?
How to Validate The Numbers in the Varchar Data Type Field? [message #201433] Sat, 04 November 2006 03:20 Go to next message
fazal_haq2001
Messages: 35
Registered: July 2006
Location: India
Member
Hi,
Ihev the difficulty to validate the below criteria...Plz help..

Eg)
I have a telephone No.Field..
0091-444-3829202
I want to validate for numbers dynamically. in frontend except the hyphon(-).

..how to do it..any special function for this one..

Regards,
Fazil...
Re: How to Validate The Numbers in the Varchar Data Type Field? [message #201509 is a reply to message #201433] Sun, 05 November 2006 07:01 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member

Suppose Table T stores teh phone nos..(in column tel_no which is varchar2)

then to do this add a check constraint like:

  1* ALTER TABLE T ADD (CONSTRAINT T_C CHECK (TO_NUMBER(REPLACE(TEL_NO,'-'))>0))
SQL> /

Table altered.

--Now try adding something invalid like:

SQL> INSERT INTO T VALUES('ASD-343');
INSERT INTO T VALUES('ASD-343')
          *
ERROR at line 1:
ORA-01722: invalid number --constraint throws out the error..



Re: How to Validate The Numbers in the Varchar Data Type Field? [message #201564 is a reply to message #201433] Sun, 05 November 2006 19:55 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Look at http://www.orafaq.com/forum/m/168856/67467/?srch=iznumeric#msg_168856 and just add '-' to the list of acceptable characters.

David
Previous Topic: attachment file
Next Topic: code to get output in pdf format
Goto Forum:
  


Current Time: Thu Dec 08 12:06:09 CST 2016

Total time taken to generate the page: 0.17052 seconds