在Postgres中将十六进制字符串转换为bigint [英] Convert hex string to bigint in Postgres
问题描述
我希望将HTML中使用的十六进制字符串转换为 bigint
,然后通过写入的函数将其转换为Postgres中的单独R,G和B值在PL / pgSQL中。
我可以将字符串解码为 bytea
,如下所示:
hex bytea:= decode(hex,'hex');
在一个固定值的查询中,这个功能就像一个美:
select(array [(cast(x'ffaa33'as bigint)>> 16)%256,
(cast(x'ffaa33' as bigint)>>> 8)%256,
cast(x'ffaa33'as bigint)%256])
但我不能把两者放在一起,例如传递'ffaa33'作为参数。
任何人有更好的主意?我使用PosgreSQL 9.1?
一个简单的方法是:
select('x'|| lpad(the_hex_value,16,'0')):: bit(64):: bigint;
用0填充左边是必要的,因为最左边的位总是被解释为符号位。
请记住, bigint
是有符号的,postgres没有内置的无符号类型。
I'd like to convert a hex string as used by HTML into a bigint
to then convert it into separate R, G and B values in Postgres via a function written in PL/pgSQL.
I can decode the string into bytea
like this:
hex bytea := decode(hex, 'hex');
And in a query with fixed values this works like a beauty:
select ( array[ (cast(x'ffaa33' as bigint) >> 16) % 256,
(cast(x'ffaa33' as bigint) >> 8) % 256,
cast(x'ffaa33' as bigint) % 256 ] )
But I can't put the two together, passing - for example 'ffaa33' as parameter.
Anyone got a better idea? I'm using PosgreSQL 9.1?
An easy way is:
select ('x'||lpad(the_hex_value,16,'0'))::bit(64)::bigint;
The left padding with 0 is necessary because the leftmost bit is always going to be interpreted as the sign bit.
Also keep in mind that bigint
is signed, postgres doesn't have built-in unsigned types.
这篇关于在Postgres中将十六进制字符串转换为bigint的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!