PostgreSQL的10進制與16進制互轉 · postgresql手冊

**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):? <table style="BORDER-TOP: rgb(248,248,248) 1px solid; BORDER-RIGHT: rgb(248,248,248) 1px solid; WIDTH: 703px; WHITE-SPACE: normal; WORD-SPACING: 0px; BORDER-COLLAPSE: collapse; BORDER-BOTTOM: rgb(248,248,248) 1px solid; TEXT-TRANSFORM: none; COLOR: rgb(68,68,68); PADDING-BOTTOM: 0px; TEXT-ALIGN: left; PADDING-TOP: 0px; FONT: 12px/22px 微軟雅黑, Verdana, sans-serif, 宋體; PADDING-LEFT: 0px; MARGIN: 0px; BORDER-SPACING: 1px; BORDER-LEFT: rgb(248,248,248) 1px solid; WIDOWS: 1; LETTER-SPACING: normal; PADDING-RIGHT: 0px; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; -webkit-text-stroke-width: 0px" bordercolor="#000000" cellspacing="0" cellpadding="1" border="1"><tbody style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"><tr style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"><td style="BORDER-TOP: rgb(248,248,248) 1px solid; BORDER-RIGHT: rgb(248,248,248) 1px solid; BORDER-BOTTOM: rgb(248,248,248) 1px solid; PADDING-BOTTOM: 1px; PADDING-TOP: 1px; PADDING-LEFT: 2px; MARGIN: 0px; BORDER-LEFT: rgb(248,248,248) 1px solid; PADDING-RIGHT: 2px">CREATE OR REPLACE FUNCTION to_hex(integer)<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? RETURNS text AS<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>'to_hex32'<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? LANGUAGE internal IMMUTABLE STRICT<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? COST 1;<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>ALTER FUNCTION to_hex(integer)<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? OWNER TO postgres;<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>COMMENT ON FUNCTION to_hex(integer) IS 'convert int4 number to hex';<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/><br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>CREATE OR REPLACE FUNCTION to_hex(bigint)<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? RETURNS text AS<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>'to_hex64'<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? LANGUAGE internal IMMUTABLE STRICT<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? COST 1;<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>ALTER FUNCTION to_hex(bigint)<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>? OWNER TO postgres;<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/>COMMENT ON FUNCTION to_hex(bigint) IS 'convert int8 number to hex';<br style="PADDING-BOTTOM: 0px; PADDING-TOP: 0px; PADDING-LEFT: 0px; MARGIN: 0px; PADDING-RIGHT: 0px"/></td></tr></tbody></table> 參考:http://postgresql.1045698.n5.nabble.com/Hex-to-Dec-Conversion-td3218223.html