-1

I have a binary code of 35 characters and have the expected output. Need the logic on how to convert that in SQL Server.

Input (binary): 00000110010110110001110101101110100
Expected output (int): 816570

Thanks in advance.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Revant
  • 17
  • 2
  • 1
    Does this answer your question? [SQL Server : how to convert binary back to int](https://stackoverflow.com/questions/41439509/sql-server-how-to-convert-binary-back-to-int) – Amira Bedhiafi Nov 20 '19 at 11:05
  • @xXx no it doesn't help, I was looking for actual logic to be put in the function – Revant Nov 20 '19 at 11:15
  • What is the type of input? varbinary or a varchar containing zeros and ones? – Salman A Nov 20 '19 at 11:24
  • Please read more about binary types : https://sqlsunday.com/2017/01/09/binary-types/ – Amira Bedhiafi Nov 20 '19 at 11:24
  • 1
    The binary value for decimal value `816570` is `11000111010110111010‬`, so this is not exact conversion between decimal and binary values. If you can, post another couple of input-output values. Thanks. – Zhorov Nov 20 '19 at 11:31

1 Answers1

1

Try below query using recursive CTE:

declare @bit varchar(50) = '00000110010110110001110101101110100'

;with cte as (
    select 0 [bit], cast(1 as bigint) powerOf2, substring(@bit, len(@bit) - 1, 1) [bitValue]
    union all
    select [bit] + 1, powerOf2 * 2, substring(@bit, len(@bit) - [bit] - 1, 1)
    from cte
    where [bit] + 1 < len(@bit)
)

select sum(powerOf2 * bitValue) from cte

It's important to make second column as bigint, so it can hold big integer values :)

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • Just ran your script and noticed it's different from the expected result? See [dbfiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e27d27fc5150c665c44b27f5fe581cce) – Attie Wagner Nov 20 '19 at 14:22
  • @Birel But it's correct conversion, OP must have wrong output value or it is not binary representation of a number. – Michał Turczyn Nov 21 '19 at 08:45