How to convert binary to integer in SQL server

Question

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.

Does this answer your question? SQL Server : how to convert binary back to int — user9517769
– user9517769, Commented Nov 20, 2019 at 11:05
@xXx no it doesn't help, I was looking for actual logic to be put in the function — Revant
– Revant, Commented Nov 20, 2019 at 11:15
What is the type of input? varbinary or a varchar containing zeros and ones? — Salman Arshad
– Salman Arshad, Commented Nov 20, 2019 at 11:24
Please read more about binary types : sqlsunday.com/2017/01/09/binary-types — user9517769
– user9517769, Commented Nov 20, 2019 at 11:24
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
– Zhorov, Commented Nov 20, 2019 at 11:31

Michał Turczyn · Accepted Answer · 2019-11-20 11:27:08Z

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 :)

answered Nov 20, 2019 at 11:27

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Attie Wagner Over a year ago

Just ran your script and noticed it's different from the expected result? See dbfiddle

Michał Turczyn Over a year ago

@Birel But it's correct conversion, OP must have wrong output value or it is not binary representation of a number.

Collectives™ on Stack Overflow

How to convert binary to integer in SQL server

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related