Thread: Conversion from CHAR HEX

hi guys,
I am trying to convert a simple char value to HEX but I am getting Error.


*MyTable:*

CREATE TABLE mytable (from_ip CHAR(20), to_ip CHAR(20));

*I have below values in the Table:*


fm_db_Server1=# select * from operator;
           from_ip        |        to_ip
--------------------+----------------------
 202.153.087.128      | 202.153.087.159
 196.192.015.000      | 196.192.015.063
(2 rows)


I am trying to extract the 1st part of the IP and convert to the HEX as
below:


fm_db_Server1=# *select to_hex(to_number(substr(from_ip, 1, 3), '999'))  as
price from operator;*
/ERROR:  function to_hex(numeric) does not exist
LINE 1: select to_hex(to_number(substr(from_ip, 1, 3), '999'))  as p...
               ^
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts./



 I am not able to figure it out what is wrong here. Can Any body point out
what is wrong here.
Thanks is advance.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Conversion-from-CHAR-HEX-tp5805115.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 05/27/2014 09:47 AM, saqibrafique wrote:

> fm_db_Server1=# *select to_hex(to_number(substr(from_ip, 1, 3), '999'))  as
> price from operator;*
> /ERROR:  function to_hex(numeric) does not exist
> LINE 1: select to_hex(to_number(substr(from_ip, 1, 3), '999'))  as p...
>                 ^

There's no to_hex function for the NUMERIC type. You also don't need the
to_number function call. And of course, your function usage will break
if there are ever less than 3 characters in your first octet.

Change your query to this:

select to_hex(split_part(from_ip, '.', 1)::INT) as price
   from operator;

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email