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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With