How to convert binary to decimal in SQL Server?

/
0 Comments
How to convert binary to decimal in SQL Server?

Today I will show you how we can write a query in order to convert Binary to Decimal in SQL Server and vice-versa:

1. Query to convert Binary to Decimal value:
DECLARE @input varchar(max) = '1010'

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

2. Query to convert Decimal to Binary value:
DECLARE @input BIGINT = 10

;WITH N(N)AS
(
    SELECT top 63
        POWER(cast(2 as bigint),
        ROW_NUMBER()over(ORDER BY (SELECT 1))-1)
    FROM
         (VALUES(1),(1),(1),(1))M(a),
        (VALUES(1),(1),(1),(1))L(a),
        (VALUES(1),(1),(1),(1))K(a)
)
SELECT
    COALESCE
    (
         REVERSE
         (
             (
                SELECT CAST(@input/N%2 as CHAR(1))
                FROM N
                WHERE N <= @input
                for xml path(''), type
             ).value('.', 'varchar(max)')
            )
         , '0'
)





About Post Author

Microsoft Certified SQL Server Developer

You may also like

No comments:

If you have any queries regarding the article or in performing something in Microsoft SQL Server then please let me know.

Followers

Popular Posts