Binary String to INT in T-SQL

Question

I have 7 bit binary data stored in a SQL Server database as varchar. This 7 bit binary data ultimately needs to be converted into an int. I was attempting to take this binary string and then convert to varbinary and then convert to integer. The cast to varbinary can accept a hex string, but I'm unable to find a way for this cast to accept a binary string.

Example strings:

0000010
0000100
0001001

These should convert to:

2
4
9

I could create a CLR function to call a C# function, but I was hoping to do this all in T-SQL.

just create a table with the 128 possible strings and values and look it up from there — Martin Smith
– Martin Smith, Commented Apr 27, 2021 at 17:32

Serg · Accepted Answer · 2021-04-27 18:31:53Z

2

Convertion by hand

select id, sum(cast(substring(str, 7-p.pos, 1) as int) * power(2, p.pos)) val
from (
  SELECT 1 id, '0001001' str union all
  SELECT 2 id, '0001011' str
) t
cross apply (
  values 
     (0)
    ,(1)
    ,(2)
    ,(3)
    ,(4)
    ,(5)
    ,(6)
  ) p(pos)
group by id

answered Apr 27, 2021 at 18:31

Serg

22.8k5 gold badges23 silver badges50 bronze badges

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

1 Comment

Mr. Cooper Over a year ago

Genius! Thank you!

Collectives™ on Stack Overflow

Binary String to INT in T-SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related