Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language:
Expertise: Beginner
Jan 4, 2002

Using the IP Address in the Select Statement


IP addresses logically are four numbers. While they are stored in the DB, they are usually held in only one field. I have encountered two types of such storage:

1) VARCHAR(15)'; /7#151; four decimal numbers with a (.) separator concatenated to a string.

2) CHAR(8)—four hexadecimal numbers concatenated to a string without any separator.

For validation purposes, direct displaying the column in GUI is not recommended. The IP value should be broken into four parts. Each of these should be validated separately as a whole number (the first number couldn't be zero). For instance, if the hexadecimal format is used for storing the IP address, the SELECT statement that returns four partial numbers will be:
 
SELECT
to_number(DECODE(substr("TABLE"."IP_ADDR",1,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',_
		substr("TABLE"."IP_ADDR",1,1))) * 16 +
to_number(DECODE(substr("TABLE"."IP_ADDR",2,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',
	substr("TABLE"."IP_ADDR",2,1))) A1,

to_number(DECODE(substr("TABLE"."IP_ADDR",3,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',
	substr("TABLE"."IP_ADDR",3,1))) * 16 +
to_number(DECODE(substr("TABLE"."IP_ADDR",4,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',
	substr("TABLE"."IP_ADDR",4,1))) A2,

to_number(DECODE(substr("TABLE"."IP_ADDR",5,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',	_
substr("TABLE"."IP_ADDR",5,1))) * 16 +
to_number(DECODE(substr("TABLE"."IP_ADDR",6,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',
	substr("TABLE"."IP_ADDR",6,1))) A3,

to_number(DECODE(substr("TABLE"."IP_ADDR",7,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',	_
substr("TABLE"."IP_ADDR",7,1))) * 16 +
to_number(DECODE(substr("TABLE"."IP_ADDR",8,1),'a','10','b',_
'11','c','12',
'd','13','e','14','f','15',	_
substr("TABLE"."IP_ADDR",8,1))) A4
    FROM "TABLE";

For update purposes, use the following statement:
UPDATE "TABLE" SET "IP_ADDR" = DECODE(to_char(Floor(A1/16,0), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Floor(A1/16,0), 'FM0')) || DECODE(to_char(Mod(A1/16), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Mod(A1/16), 'FM0')) || DECODE(to_char(Floor(A2/16,0), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Floor(A2/16,0), 'FM0')) || DECODE(to_char(Mod(A2/16), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Mod(A2/16), 'FM0')) || DECODE(to_char(Floor(A3/16,0), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Floor(A3/16,0), 'FM0')) || DECODE(to_char(Mod(A3/16), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Mod(A3/16), 'FM0')) || DECODE(to_char(Floor(A4/16,0), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Floor(A4/16,0), 'FM0')) || DECODE(to_char(Mod(A4/16), 'FM0'),'10','a','11','b','12','c','13','d','14','e','15','f', to_char(Mod(A4/16), 'FM0')) This technique comes from the idea of using Decode function for queries ("Use Decode Function for Oracle Queries" by Trupti Rajparia from Phoenix, Arizona). The proposed idea allows for the separation of the DB query and GUI validation.
Lev Romanov
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date