Function to convert Binary to Decimal

[Luciano Rodrigues Nunes Mendes]

Hi Guys,

Does anyone know if Firebird 3 has any function that converts a number in binary format to decimal format?

ex.:

SELECT BINARY_TO_DECIMAL('10101010101010101010') FROM RDB$DATABASE

returned value:

699050

Best Regards,

Luciano

[Mark Rotteveel]

On 06-03-2021 13:27, Luciano Rodrigues Nunes Mendes wrote:
> Does anyone know if Firebird 3 has any function that converts a number
> in binary format to decimal format?
>
> ex.:
> SELECT BINARY_TO_DECIMAL('10101010101010101010') FROM RDB$DATABASE
> returned value:
> 699050

The Firebird 3.0 Language Reference, chapter Built-in Scalar
Functions[1] lists all functions that Firebird 3.0 has built-in.

As you can see, there is no such function available in Firebird. You
will need to build one yourself in a PSQL function (eg using substring
and the bin_XXX functions), or build a UDR.

Mark

[1]:
https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-functions
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Hi Mark,

Thank you very much for your prompt reply!

Does firebird use any naming conventions for functions? I ask this because I want to avoid that the functions I create may conflict with future versions of Firebird.

I created the following function, M_BINTODEC, to transform numbers in binary to decimal format:

SET TERM ^ ;

CREATE OR ALTER FUNCTION M_BINTODEC (BIN_NUMBER VARCHAR(63)) RETURNS BIGINT

AS

DECLARE DEC_NUMBER BIGINT DEFAULT 0;

DECLARE BIT_POSITION SMALLINT;

BEGIN

BIT_POSITION=CHAR_LENGTH(BIN_NUMBER);

WHILE (BIT_POSITION>0) DO

  BEGIN

  SELECT :DEC_NUMBER+IIF(SUBSTRING(:BIN_NUMBER FROM :BIT_POSITION for 1)='1',POWER(2,CHAR_LENGTH(:BIN_NUMBER)-:BIT_POSITION),0) FROM RDB$DATABASE INTO :DEC_NUMBER;

  BIT_POSITION=BIT_POSITION-1;

  END

RETURN DEC_NUMBER;

END^

SET TERM ; ^

Is there any possibility to simplify this function using the bin_XXX as you suggested?

Thanks in advance,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/a399eaa1-8a9b-33e9-df19-fb3a5aaaa25d%40lawinegevaar.nl.

[Mark Rotteveel]

On 06-03-2021 15:42, Luciano Rodrigues Nunes Mendes wrote:
> Thank you very much for your prompt reply!
>
> Does firebird use any naming conventions for functions? I ask this
> because I want to avoid that the functions I create may conflict with
> future versions of Firebird.

There is no naming convention. The only convention that exists is that
we strive to use names of functions as defined in the ISO 9075-2
standard (e.g ISO 9075-2:2016 aka SQL:2016 Foundation), or if a function
is not defined in the standard, we try to use a logical name as used in
other database products

> I created the following function, M_BINTODEC, to transform numbers in
> binary to decimal format:
>
> SET TERM ^ ;
> CREATE OR ALTER FUNCTION M_BINTODEC (BIN_NUMBER VARCHAR(63)) RETURNS BIGINT
> AS
> DECLARE DEC_NUMBER BIGINT DEFAULT 0;
> DECLARE BIT_POSITION SMALLINT;
> BEGIN
> BIT_POSITION=CHAR_LENGTH(BIN_NUMBER);
> WHILE (BIT_POSITION>0) DO
>   BEGIN
>   SELECT :DEC_NUMBER+IIF(SUBSTRING(:BIN_NUMBER FROM :BIT_POSITION for
> 1)='1',POWER(2,CHAR_LENGTH(:BIN_NUMBER)-:BIT_POSITION),0) FROM
> RDB$DATABASE INTO :DEC_NUMBER;
>   BIT_POSITION=BIT_POSITION-1;
>   END
> RETURN DEC_NUMBER;
> END^
> SET TERM ; ^
>
> Is there any possibility to simplify this function using the bin_XXX as
> you suggested?

You don't need the select ... into, you can use

DEC_NUMBER = DEC_NUMBER + IIF(SUBSTRING(BIN_NUMBER FROM BIT_POSITION for
> 1)='1', POWER(2, CHAR_LENGTH(BIN_NUMBER) - BIT_POSITION), 0);

Also, assign CHAR_LENGTH(BIN_NUMBER) to a 'constant' variable, so you
don't determine the length for each iteration.

Use of the bin_XXX will probably not make this simpler:

create or alter function m_bintodec(bin_number varchar(63))
returns bigint
as
declare dec_number bigint default 0;
declare bit_position smallint default 0;
declare bin_length smallint;
begin
bin_length = char_length(bin_number);
while (bit_position < bin_length) do
begin
bit_position = bit_position + 1;
dec_number = bin_or(
bin_shl(dec_number, 1),
decode(
substring(bin_number from bit_position for 1),
'0', 0,
'1', 1));
end
return dec_number;
end

Note that this function will return NULL when bin_number is not binary.

Mark
--
Mark Rotteveel

[Luciano Rodrigues Nunes Mendes]

Thank you very much Mark!

Best Regards,

Luciano

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/3b6f18ac-c80a-438c-6ad6-717080099e9d%40lawinegevaar.nl.