T-SQL: Convert '0110' to 6

Question

Is there any good way convert a binary (base 2) strings to integers in T-SQL (if not I guess I can write a function...)

'0110' => 6 etc.

Answer 1

Do you know that the strings will always be a particular length? If so you could hard-code a bunch of Substring/convert-to-int/multiply steps. It wouldn't exactly be nice though...

EDIT: To give a bit more detail, suppose you know it's going to be length 4, you could do:

SELECT (8 * CONVERT(int, SUBSTRING(@x, 1, 1)))
     + (4 * CONVERT(int, SUBSTRING(@x, 2, 1)))
     + (2 * CONVERT(int, SUBSTRING(@x, 3, 1)))
     + (1 * CONVERT(int, SUBSTRING(@x, 4, 1)))

Apologies if the syntax is slightly off.

Unpleasant, and doesn't work in the more general case (where you'd basically need to loop in some form) but it might be good enough for a quick fix.

(If downvoters could please add comments, it would be much appreciated. T-SQL is somewhat outside my realm of expertise - explanations of why this is a bad idea are much more helpful than just the downvote to indicate that it is a bad idea.)

Answer 2

Take a look at this blog post by Peter DeBetta; you could use this as a starting point.

Answer 3

I had something vaguely similar HERE (StackOverflow), where I was asking about a customised 'base' conversion in T-SQL.

It will be interesting to see what suggestions you get!

Answer 4

Looks like this function does what you need. Also, looks like there is an optimisation here.

I don't know of anything built-in that will work in a select statement, casting to binary or varbinary is no help.

Looks like you are stuck either writing this as a UDF or an extended stored proc (managed or native).

Another option could be creating a huge lookup table and joining into it. It is possible you will get better performance than an Sql function, I have not tested it. I doubt it would perform better than an extended stored proc though.

Answer 5

Oki, well this is a first attempy and quick hack and trying to solve this. Its certainly not pretty, but might give you some ideas on how to best solve the problem.

EDIT: just reread sambo99 answer and noted the optimized version link referred to there. I missed that before answering. It appears to be somewhat similar, but this one is good up to 63 bits, where as that referred link is only good for up to 31 bits, and will blowup if you try to pass it more. This version ignores anything higher than 63.

DECLARE @bin varchar(63)
DECLARE @origlen int
DECLARE @currlen int
DECLARE @tmp int
DECLARE @pwr bigint
DECLARE @dec bigint
DECLARE @val bigint

SET @pwr = 2
SET @dec = 0
SET @bin = '011111111111111111111111111111111' --Test Value

SET @bin = REVERSE(@bin)
SET @origlen = LEN(@bin)
SET @currlen = 0

WHILE(@currlen <> @origlen)
BEGIN
    SET @tmp = CONVERT(int, SUBSTRING(@bin, @currlen + 1, 1))

    IF (@tmp <> 0)
    BEGIN
     SET @val = @dec
     SET @dec = POWER(@pwr, @currlen)
     SET @val = @val + @dec
     Set @dec = @val
    END

    SET @currlen = @currlen + 1

END

SELECT @val as 'Result'

Hope this helps.