By using front end tools validation of such strings is easy.

If your ip-address is in theformat xxx.xxx.xxx.xxx (i.e. 123.123.123.123)

In the before insert - update trigger conver your string to 999.999.999.999
format.
By padding zeros.

ALTER TABLE table_name
ADD CONSTRAINT check_IP_addr CHECK (
TRANSLATE('ip_string', '1234567890.', '9999999999.') = '999.999.999.999' AND
SUBSTR(ip_string, 1, 3) BETWEEN 0 AND 255 AND
SUBSTR(ip_string, 5, 3) BETWEEN 0 AND 255 AND
SUBSTR(ip_string, 9, 3) BETWEEN 0 AND 255 AND
SUBSTR(ip_string, 13, 3) BETWEEN 0 AND 255 )

In check constraint one can not use or if or case statement.
This will be too complicated

ALTER TABLE table_name
ADD CONSTRAINT check_IP_addr CHECK (
TO_NUMBER(SUBSTR(ip_string, 1, INSTR(ip_string, '.', 1, 1) - 1) BETWEEN 0
AND 255 AND
TO_NUMBER( SUBSTR(ip_string, INSTR(ip_string, '.', 1, 1),
INSTR(ip_string, '.', 1, 2) - INSTR(ip_string, '.', 1, 1) - 1
BETWEEN 0 AND 255 AND
TO_NUMBER( SUBSTR(ip_string, INSTR(ip_string, '.', 1, 2),
INSTR(ip_string, '.', 1, 3) - INSTR(ip_string, '.', 1, 2) - 1
BETWEEN 0 AND 255 AND
TO_NUMBER( SUBSTR(ip_string, INSTR(ip_string, '.', 1, 1),
LENGTH(ip_string) - INSTR(ip_string, '.', 1, 1) - 1
BETWEEN 0 AND 255
)


Please check the result, I have tried it long back.

Sucheta


----- Original Message -----
From: "bernas_glen"
<oracledba-ezmlmshield-x84994506.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, December 29, 2005 10:40 PM
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
>
>
> --
> This message has been scanned for viruses and
> dangerous content, and is believed to be clean.
>


Oracle LazyDBA home page