Converting integer -> IP Address

#1

Hello all.

DB2 LUW V9.5 Fp5

Anyone knows a way to transform a integer in a ip address using Scalar
Function?

Regards

Bruno.

#2

On 2010-09-27 19:30, brunoalsantos wrote:

Quote:

Hello all.

DB2 LUW V9.5 Fp5

Anyone knows a way to transform a integer in a ip address using Scalar
Function?

Is it something like this that you mean?

create function bigint_to_ip (ip bigint)
returns varchar(15)
begin atomic

declare fst bigint;
declare snd bigint;
declare trd bigint;
declare fth bigint;

set fst = ip / (256*256*256);
set snd = mod(ip,256*256*256) / (256*256);
set trd = mod(ip,256*256) / 256;
set fth = mod(ip,256);

return rtrim(cast(fst as char(3))) || '.' ||
rtrim(cast(snd as char(3))) || '.' ||
rtrim(cast(trd as char(3))) || '.' ||
rtrim(cast(fth as char(3)));

end @

db2 "values bigint_to_ip(3555336315)"

1
---------------
211.234.32.123

/Lennart

Quote:

Regards

Bruno.

#3

On Sep 28, 3:29*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2010-09-27 19:30, brunoalsantos wrote:

Hello all.

DB2 LUW V9.5 Fp5

Anyone knows a way to transform a integer in a ip address using Scalar
Function?

Is it something like this that you mean?

create function bigint_to_ip (ip bigint)
returns varchar(15)
begin atomic

* * declare fst bigint;
* * declare snd bigint;
* * declare trd bigint;
* * declare fth bigint;

* * set fst = ip / (256*256*256);
* * set snd = mod(ip,256*256*256) / (256*256);
* * set trd = mod(ip,256*256) / 256;
* * set fth = mod(ip,256);

* * return rtrim(cast(fst as char(3))) || '.' ||
* * * * * *rtrim(cast(snd as char(3))) || '.' ||
* * * * * *rtrim(cast(trd as char(3))) || '.' ||
* * * * * *rtrim(cast(fth as char(3)));

end @

The fuction body can be written in a RETURN statement.

CREATE FUNCTION bigint_to_ip(ip BIGINT)
RETURNS VARCHAR(15)
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
RTRIM( CHAR( ip / (256*256*256) ) ) || '.' ||
RTRIM( CHAR( MOD(ip , 256*256*256) / (256*256) ) ) || '.' ||
RTRIM( CHAR( MOD(ip , 256*256) / 256 ) ) || '.' ||
CHAR( MOD(ip , 256) )
;

#4

On 27 set, 15:29, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2010-09-27 19:30, brunoalsantos wrote:

Hello all.

DB2 LUW V9.5 Fp5

Anyone knows a way to transform a integer in a ip address using Scalar
Function?

Is it something like this that you mean?

create function bigint_to_ip (ip bigint)
returns varchar(15)
begin atomic

* * declare fst bigint;
* * declare snd bigint;
* * declare trd bigint;
* * declare fth bigint;

* * set fst = ip / (256*256*256);
* * set snd = mod(ip,256*256*256) / (256*256);
* * set trd = mod(ip,256*256) / 256;
* * set fth = mod(ip,256);

* * return rtrim(cast(fst as char(3))) || '.' ||
* * * * * *rtrim(cast(snd as char(3))) || '.' ||
* * * * * *rtrim(cast(trd as char(3))) || '.' ||
* * * * * *rtrim(cast(fth as char(3)));

end @

db2 "values bigint_to_ip(3555336315)"

1
---------------
211.234.32.123

/Lennart

Regards

Bruno.- Ocultar texto das mensagens anteriores -

- Mostrar texto das mensagens anteriores -

Thanks Lennart.

#5

If you upgraded to DB2 LUW V9.7,
the expression could be written a little shorter by using
VARCHAR(bigint) built-in function, like this:

CREATE FUNCTION bigint_to_ip_v97(ip BIGINT)
RETURNS VARCHAR(15)
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN
VARCHAR( ip / (256*256*256) ) || '.' ||
VARCHAR( MOD(ip , 256*256*256) / (256*256) ) || '.' ||
VARCHAR( MOD(ip , 256*256) / 256 ) || '.' ||
VARCHAR( MOD(ip , 256) )
;

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Converting integer -> IP Address

comp.databases.ibm-db2 comp.databases.ibm-db2

Converting integer -> IP Address - 09-27-2010 , 12:30 PM

Re: Converting integer -> IP Address - 09-27-2010 , 01:29 PM

Re: Converting integer -> IP Address - 09-27-2010 , 01:56 PM

Re: Converting integer -> IP Address - 09-27-2010 , 02:00 PM

Re: Converting integer -> IP Address - 09-27-2010 , 02:04 PM