Postgres的10进制与16进制互相转换
Postgres的10进制与16进制互相转换 1.10进制转16进制 Postgres里面有一个内置的10进制转16进制的函数:to_hex(int)/to_hex(bigint) [postgres@localhost ~]$ psql Password: psql (9.1.3) Type help for help. postgres=# select to_hex(9); to_hex --------
Postgres的10进制与16进制互相转换
1.10进制转16进制
Postgres里面有一个内置的10进制转16进制的函数:to_hex(int)/to_hex(bigint)
[postgres@localhost ~]$ psql
Password:
psql (9.1.3)
Type "help" for help.
postgres=# select to_hex(9);
to_hex
--------
9
(1 row)
postgres=# select to_hex(17);
to_hex
--------
11
(1 row)
postgres=# select to_hex(31);
to_hex
--------
1f
(1 row)
postgres=# select to_hex(255);
to_hex
--------
ff
(1 row)
postgres=# select to_hex(256);
to_hex
--------
100
(1 row)
2.16进制转10进制
没有内置的，参考网上的一个例子
postgres=# CREATE FUNCTION
hex_to_dec(in_hex TEXT)
RETURNS INT
IMMUTABLE STRICT LANGUAGE sql AS
$body$
SELECT CAST(CAST(('x' || CAST($1 AS text)) AS bit(8)) AS INT);
$body$;
CREATE FUNCTION
postgres=# select hex_to_dec('1f');
hex_to_dec
------------
31
(1 row)
postgres=# select hex_to_dec('ff');
hex_to_dec
------------
255
(1 row)
postgres=# select hex_to_dec('fe');
hex_to_dec
------------
254
(1 row)
postgres=# select hex_to_dec('09');
hex_to_dec
------------
9
(1 row)
postgres=# select hex_to_dec('11');
hex_to_dec
------------
17
(1 row)
内置的函数说明,支持输入是int或者bigint类型
to_hex(int/bigint):
CREATE OR REPLACE FUNCTION to_hex(integer)
RETURNS text AS
'to_hex32'
LANGUAGE internal IMMUTABLE STRICT
COST 1;
ALTER FUNCTION to_hex(integer)
OWNER TO postgres;
COMMENT ON FUNCTION to_hex(integer) IS 'convert int4 number to hex';
CREATE OR REPLACE FUNCTION to_hex(bigint)
RETURNS text AS
'to_hex64'
LANGUAGE internal IMMUTABLE STRICT
COST 1;
ALTER FUNCTION to_hex(bigint)
OWNER TO postgres;
COMMENT ON FUNCTION to_hex(bigint) IS 'convert int8 number to hex';