I am using SQL Server 2012 Express. I have a string of 1's and 0's 32 bits in length.
01010010000100010111001101110011
How would I convert that to a Signed Decimal Number in a SQL script?
Currently I use a Web Tool online for my answer, and my current searching is not leading me to the answer I need.
You can perform the conversion with a single T-SQL statement if you use a Tally table:
;WITH Tally(i) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
FROM (VALUES (0), (0), (0), (0), (0), (0), (0), (0)) a(n)
CROSS JOIN (VALUES (0), (0), (0), (0)) b(n)
)
SELECT SUM(t.v) AS DecimalNumber
FROM (
SELECT POWER(CAST(SUBSTRING(x.d, i, 1) AS DECIMAL(10,0)) * 2, 32 - i)
FROM (VALUES ('01010010000100010111001101110011')) x(d)
CROSS JOIN Tally) AS t(v)
Explanation:
Tally is a table expression returning all values from 1-32. SUBSTRING.POWER mathematical function we can convert every separate binary digit to decimal.SUM we can add up all separate decimal numbers to get the expected result. You can try like below -
DECLARE @Binary VARCHAR(100) = '01010010000100010111001101110011';
DECLARE @characters CHAR(36),
@result BIGINT,
@index SMALLINT,
@base BIGINT;
SELECT @characters = '0123456789abcdefghijklmnopqrstuvwxyz',
@result = 0,
@index = 0,
@base = 2;
WHILE @index < LEN(@Binary)
BEGIN
SELECT @result = @result + POWER(@base, @index) * (CHARINDEX(SUBSTRING(@Binary, LEN(@Binary) - @index, 1), @characters) - 1);
SET @index = @index + 1;
END
SELECT @result;
This will help you to convert from any base ( I used@base as 2 for binary) to base 10. Started from far right and moved to the left until we run out of digits. The conversion is the (base ^ index) * digit.
in Addition to Giorgos Betsos's reply see ITVF function below:
CREATE FUNCTION [dbo].[udf_BinaryToDecimal]
(
@Binary VARCHAR(31)
)
RETURNS TABLE AS RETURN
WITH Tally (n) AS
(
--32 Rows
SELECT TOP (LEN (@Binary)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
FROM (VALUES (0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
SELECT
SUM(SUBSTRING(REVERSE(@Binary),n+1,1) * POWER(2,n)) TenBase
FROM Tally
/*How to Use*/
SELECT TenBase
FROM udf_BinaryToDecimal ('01010010000100010111')
/*Result -> 336151*/
I had to add this code to the end of Abhishek's Code Example to get a Signed number I needed.
DECLARE @MyNewExValue INT
IF @result > 2147483647
BEGIN
SET @result = @result - (2147483648 * 2)
END
SET @MyNewExValue = @result
SELECT @MyNewExValue
This solution will work with binary strings of any (bigint) length:
DECLARE @input varchar(max) =
'01010010000100010111001101110011'
;WITH N(V) AS
(
SELECT
ROW_NUMBER()over(ORDER BY (SELECT 1))
FROM
(VALUES(1),(1),(1),(1))M(a),
(VALUES(1),(1),(1),(1))L(a),
(VALUES(1),(1),(1),(1))K(a)
)
SELECT SUM(SUBSTRING(REVERSE(@input),V,1)*POWER(CAST(2 as BIGINT), V-1))
FROM N
WHERE V <= LEN(@input)
(source)
It should be noted the most upvoted solution above only works with binary strings exactly 32 bits in length, and a string of '00000000000000000000000000000000' returns 1.