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:

 SELECTto_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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: