Issue
I am trying to convert bit varying
to numeric
(decimal value) in postgresql
select cast('10110101110110010100110001101010100111011011010000000101'::bit varying as numeric);
I have tried the cast
function but getting below error.
ERROR: cannot cast type bit varying to numeric LINE 1: select cast('10110101110110010100110001101010100111011011010... ^ ********** Error **********
ERROR: cannot cast type bit varying to numeric SQL state: 42846 Character: 8
Output should be : 5429503678976
. Any suggestions
EDIT: I do not know the max value so can not use
select b'10110101110110010100110001101010100111011011010000000101'::bigint
Thanks
Solution
You could break it up into individual bits, convert those to the appropriate numeric values and then sum them together:
with data(d) as (VALUES (B'10110101110110010100110001101010100111011011010000000101') )
select sum(parts) FROM (
SELECT get_bit(d, length(d) - n)::numeric * pow(2::numeric, n-1) as parts
from data, generate_series(1, length(d), 1) g(n)
) sub
;
sum
------------------------------------
51185893014090757.0000000000000000
(1 row)
Note that this gives me the same answer as select b'10110101110110010100110001101010100111011011010000000101'::bigint;
but it should work for larger values as well, where bigint would be out of range.
You could turn it into a function:
CREATE OR REPLACE FUNCTION bitstring_to_numeric(l_bit_string varbit)
RETURNS numeric AS $$
SELECT sum(parts) FROM (
SELECT get_bit(l_bit_string,
length(l_bit_string) - n)::numeric * pow(2::numeric, n-1) as parts
from generate_series(1, length(l_bit_string), 1) g(n)
) sub
$$ LANGUAGE SQL
IMMUTABLE;
select bitstring_to_numeric(B'10110101110110010100110001101010100111011011010000000101');
bitstring_to_numeric
------------------------------------
51185893014090757.0000000000000000
Answered By - Jeremy Answer Checked By - Mary Flores (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.