check constraint

[kavithared]

Hiii friends silly question,
I am need a check validation for mobilenumber column.
any user can register to my mobile one through on line,they should enter mobilenumber.
ex:0162701817
0162782345 etc.
Here i want check constraint validation ,any one enter mobilenumber,my check constraint should check the first three numbers 016 only,So i want check validation for this column.
thanks
kavitha

[sursujmad]

Hi,

Try this..

alter table table_name
add constraint check_001
check (mobilenumber like '016%');

[SANJAY_G]

or...

alter table table_name 
add constraint check_001 
check (substr(mobilenumber,1,3) = '016');

Sanjay

[kavithared]

hii thanks,
and I need 2 validations ,the user must enter with 016 and should enter 10numbers of the mobile.
like validation one is 016
second one 0162701817 i.e should enter 10 digits.
thanks

[SANJAY_G]

alter table table_name 
add constraint check_002 
check (length(mobilenumber) = 10);

Sanjay

[nishant]

Hi,

alter table table_name
add constraint check_001
check (substr(mobilenumber,1,3) = '016' and length(mobilenumber)=10);

Are u working as DBA !!!

Take Care.

[sharmila]

kavitha

how will handle the situation wherein alphanumeric characters are entered into your mobile number.. what if a user enters sone junk /special chars ???

[Sonia]

anything other than 016 is invalid.....no matter numeric or alphanumeric
and moreover column datatype in number

[nishant]

Originally posted by Sonia
anything other than 016 is invalid.....no matter numeric or alphanumeric
and moreover column datatype in number

Hi,

Sharmila is right..the column is varchar type and not number type.In number type u cannot store value starting from 0.There is no check to see whether a number is entered or a character.

Take Care.