You didn't mention the version, but in 10g the best way is via regexp '1to3digit#' followed by a '.' followed by etc.


-----Original Message-----
From: bernas_glen [mailto:oracledba-ezmlmshield-x84994506.[Email address protected]
Sent: Thursday, December 29, 2005 12:10 PM
To: LazyDBA Discussion
Subject: IP check

I asked this question before but about how to check entries to an IP, I'm
still having a problem


This constraint work fine when you enter xxx.xxx.xxx.xxx (i.e.
123.123.123.123)
ALTER TABLE table_name
ADD CONSTRAINT check_IP_addr CHECK (
LENGTH(ip_string) = 15 AND
INSTR(ip_string, '.', 1, 1) = 4 and INSTR(ip_string, '.', 1, 2) = 8 and
INSTR(ip_string, '.', 1, 3) = 2 AND
TO_NUMBER(SUBSTR(ip_string, 1, 3)) BETWEEN 0 AND 255 AND
TO_NUMBER(SUBSTR(ip_string, 5, 3)) BETWEEN 0 AND 255 AND
TO_NUMBER(SUBSTR(ip_string, 9, 3)) BETWEEN 0 AND 255 AND
TO_NUMBER(SUBSTR(ip_string, 13, 3)) BETWEEN 0 AND 255

But what if the number is only 1 digits or 2 digits numbers (i.e 1.1.1.1 i.e
100,10,10,1)

The instr command will find the wrong position to enter the "." unless we
enter 001.001.001.001

I guess I can add padding to the entry to ensure that it's always 3 digits
(i.e. 001.001.001.001) but is there another way to determine the number of
digits between each '.' and then insure that each number is between 0 and
255??







____________________________________________

Glen Bernas
Database Administrator
EMC²
where information lives

Phone:
Direct: (508) 249-2237
Ext: 42237
<<bernas, glen.vcf>>


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page