how to transform a number in binary representation to a Snowflake number

Snowflake does not provide number or Integer to Binary function out of the box, however these UDF function can be used instead

I also overloaded the UDF in the event a string gets passed.

CREATE OR REPLACE FUNCTION int_to_binary(NUM VARIANT)
      RETURNS string
      LANGUAGE JAVASCRIPT
      AS $$
      return (NUM >>> 0).toString(2);
      $$;
 
CREATE OR REPLACE FUNCTION int_to_binary(NUM STRING)
      RETURNS string
      LANGUAGE JAVASCRIPT
      AS $$
      return (NUM >>> 0).toString(2);
      $$; 

I tried with a SQL pure UDF - it worked at first, but not when using it with data over a table.

So I had to create a Javascript UDF:

create or replace function bin_str_to_number(a string)
  returns float
  language javascript
  as
  $$
    return parseInt(A, 2)
  $$
;
select bin_str_to_number('110');

For the record, this is the error I got when attempting a pure SQL UDF for the same:

SQL compilation error: Unsupported subquery type cannot be evaluated

The UDF:

create or replace function bin_str_to_number(a string)
  returns number
  as
  $$
    (select sum(value::number*pow(2,index))::number 
     from table(flatten(input=>split_string_to_char(reverse(a)))))
  $$

That was a fun challenge for this morning! If you want to do it in pure SQL:

with binary_numbers as (
    select column1 as binary_string
    from (values('10100110'), ('101101101'), ('1010011010'), ('1011110')) tab
)

select
    binary_string,
    sum(to_number(tab.value) * pow(2, (tab.index - 1))) decimal_number
from
     binary_numbers,
     table(split_to_table(trim(replace(replace(reverse(binary_numbers.binary_string), '1', '1,'), '0', '0,' ), ','), ',')) tab
group by binary_string

Produces: