CODEWITHSUNDEEP
sqlsql-serverbinary
Steven M
Steven M

Reputation: 594

How to convert from base-2 string (e.g. '001011') to binary data type?

I have a small binary number as a string 001011 and I wish to convert it to binary or varbinary (i.e. 0xB). How would I do this in MS SQL Server?

Upvotes: 3

Views: 1490

Answers (4)

T I
T I

Reputation: 9933

CREATE FUNCTION dbo.fnConvertToBinary
    (@str VARCHAR(32), @base TINYINT)
RETURNS VARBINARY(32)
AS
BEGIN
DECLARE @bytes INT = 0
DECLARE @i INT = 0
DECLARE @l INT = LEN(@str)

IF (@base = 2)
BEGIN
    WHILE @i <= @l
    BEGIN   
        SET @bytes = @bytes * 2 + CAST(SUBSTRING(@str, @i, 1) AS TINYINT)
        SET @i = @i + 1
    END
END

IF (@base = 8)
BEGIN
    WHILE @i <= @l
    BEGIN   
        SET @bytes = (CAST(SUBSTRING(@str, @i, 1) AS TINYINT) 
                            * POWER(8, @l-@i)) 
                            + @bytes
        SET @i = @i + 1
    END
END

IF (@base = 10)
    SET @bytes = CAST(@str AS INT)

IF (@base = 16)
BEGIN
    SET @i = @l
    WHILE @i > 0
    BEGIN       
        DECLARE @bit INT = (CASE SUBSTRING(@str, @i, 1)
                            WHEN '0' THEN 0 WHEN '1' THEN 1
                            WHEN '2' THEN 2 WHEN '3' THEN 3
                            WHEN '4' THEN 4 WHEN '5' THEN 5
                            WHEN '6' THEN 6 WHEN '7' THEN 7
                            WHEN '8' THEN 8 WHEN '9' THEN 9
                            WHEN 'A' THEN 10 WHEN 'B' THEN 11
                            WHEN 'C' THEN 12 WHEN 'D' THEN 13
                            WHEN 'E' THEN 14 WHEN 'F' THEN 15 END) 

        SET @bytes = @bit * POWER(16, @l-@i) + @bytes

        SET @i = @i - 1
    END
END

RETURN CAST(@bytes AS VARBINARY(32))
END
GO

Upvotes: 1

Mark S. Rasmussen
Mark S. Rasmussen

Reputation: 35476

CREATE FUNCTION [dbo].[BinaryToDecimal]
(
    @Input varchar(255)
)
RETURNS bigint
AS
BEGIN

    DECLARE @Cnt tinyint = 1
    DECLARE @Len tinyint = LEN(@Input)
    DECLARE @Output bigint = CAST(SUBSTRING(@Input, @Len, 1) AS bigint)

    WHILE(@Cnt < @Len) BEGIN
        SET @Output = @Output + POWER(CAST(SUBSTRING(@Input, @Len - @Cnt, 1) * 2 AS bigint), @Cnt)

        SET @Cnt = @Cnt + 1
    END

    RETURN @Output  

END

SELECT CAST(dbo.BinaryToDecimal('001011') AS binary(1))

Fiddle here (note that it outputs binary data in integer form (11 = 0xB)):
http://sqlfiddle.com/#!6/c64ad/8

I've previously blogged about the BinaryToDecimal function here:
http://improve.dk/converting-between-base-2-10-and-16-in-t-sql/

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Here is my solution for strings that have up to six digits:

select cast((POWER(2, 5)*(ascii(substring(val, 6-5, 1)) - ASCII('0')) +
             POWER(2, 4)*(ascii(substring(val, 6-4, 1)) - ASCII('0')) +
             POWER(2, 3)*(ascii(substring(val, 6-3, 1)) - ASCII('0')) +
             POWER(2, 2)*(ascii(substring(val, 6-2, 1)) - ASCII('0')) +
             POWER(2, 1)*(ascii(substring(val, 6-1, 1)) - ASCII('0')) +
             POWER(2, 0)*(ascii(substring(val, 6-0, 1)) - ASCII('0'))
            ) as BINARY(2))
from (select '001011' as val) t

I've written it this way so it is obvious how to generalize it to longer strings. If you go beyond 15, then increase the size of the binary().

Upvotes: 0

Kyle Hale
Kyle Hale

Reputation: 8120

UPDATE: Wasn't thinking about the "binary" part of the string.

One method, using a numbers table:

 with c(a) as (
 select 1
 union
 select 2
 union 
 select 3
 union
 select 4
 union
 select 5
  union
 select 6
 )


select cast( sum ( substring('001011', a, 1) * power(2, len('001011') - a) ) as varbinary(10)) from c

Upvotes: 0

Related Questions