I have a DECLARE @binarySequence varchar(8) = '00000000';. I use STUFF to manually change the bit value in each index. That code is not relevant for this question though.Once I change all the necessary bits, then SELECT @binarySequence; returns '01001001'. Is there a way for me to change that so that it returns the decimal representation (73) of that binary sequence without looping through each char? All advice is welcome as well as any answers/questions Thank you for your time!

CodePudding user response：

Signed Magnitude Representation Binaries

This is something I wrote a couple of years ago. It uses a Tally to split the value into its individual characters, and then aggregates each value at their relevant POWER to get the final result:

CREATE FUNCTION [dbo].[SignedBinaryToDec] (@Binary varchar(64)) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@Binary)-1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3)
    SELECT SUM(SS.C * POWER(CONVERT(decimal(2,0),2),T.I-1)) *
           CASE LEFT(@Binary,1) WHEN 0 THEN 1
                                WHEN 1 THEN -1
           END AS Dec
    FROM Tally T
         CROSS APPLY (VALUES(SUBSTRING(REVERSE(@Binary),T.I,1)))SS(C)
    WHERE @Binary NOT LIKE '%[^0-1]%';
GO

SELECT Dec
FROM dbo.SignedBinaryToDec('01001001');

How this works

The above is a inline-table value function. This means that it returns a dataset, rather than a scalar value, and you refer to it in the FROM. The reason for this is that multi-line scalar functions can perform quite poorly. In SQL Server 2019 it can inline user defined scalar functions, however, I'm not sure if such a query would be. As such I wouldn't want to take the chance of using it, when an iTVF with set based logic will be performant.

Let's break this into a few steps:

The Tally

The first part of the query you'll see is the 2 Common Table Expressions (CTEs); N and Tally. N literally just contains 4 rows with the value NULL. Why 4? I'll explain later. Why NULL? Well, it can be any arbitrary value, I just use NULL as its values are meaningless.

Next we have the CTE Tally. Firstly you'll note that it references N in the FROM 3 times; this means that N is CROSS JOINed (using the old ANSI-89 syntax, yes) to itself 3 times, resulting in (at most) 64 rows, 4^3 = 64, which is (you'll notice) the length of the parameter, varchar(64). That is why I used 4 NULL values.

In the SELECT I limit the number of rows to be returned to the length of the varchar value -1. -1 because the binary value is signed, so the first digit in the string won't be used to determine the aggregate value (done later), but to denote if the value is positive or negative.

Finally we have ROW_NUMBER which unsurprisingly gives each row an ascending number, starting from 1. (SELECT NULL) is in the ORDER BY here as we (again) need some arbitrary value.

If we were to stop at here, with your value, this would result in a data set containing 7 rows, with the values 1 to 7. You can test this with the below

DECLARE @Binary varchar(64) = '01001001';

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@Binary)-1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3)
    SELECT I
    FROM Tally;

The outer SELECT

We'll go to the FROM again first. Obviously FROM Tally returns the rows from the CTE Tally, which we just discussed. Next we have the CROSS APPLY to a VALUES table construct. This returns each individual character for the varchar on a separate row. Note we REVERSE the values as you the lowest denomimated is on the right in numbers. If you were to return the results for your value from just the FROM you would end up with the following data set:

Note
REVERSE was not in my original solution, not sure how I missed that, but as the value was a palindrome we "lucked" out.

The WHERE is there to stop the query trying to process any invalid values. For example, if you were to input '01001a01' or '013240101' the function would not return a result.

Now the SELECT. We'll break this down.

• POWER(CONVERT(decimal(2,0),2),T.I-1)
  There a few bits here. Firstly we take the int value 2 and convert it specifically to a decimal(2,0). Then we have POWER which "does what it says on the tin"; it takes the first value and puts it to the power of the second. So, for the first row (in the above data set), that would be POWER(2,1-1) which is 1. Then we have POWER(2,2-1), POWER(2,3-1), POWER(2,4-1), which are 2, 4, and 8 respectively. As you can tell, these are your binary numbers.
• SUM(SS.C * POWER(CONVERT(decimal(2,0),2),T.I-1))
  Here we taking the expression before and multiplying it by the digit from the varchar and then aggregate those values. For your example, this means you have an expression that resolves into something like: SUM((1*1) (0*2) (0*4) (1*8) (0*16) (0*32) (1*64)) = SUM(1 0 0 8 0 0 64) = SUM(1 8 64) = 73
• The CASE expression
  This literally just inspects the left most character of the varchar. If it's 0 it multiplies the value in the previous step by 1 (unchanging the value), and if it's 1 then by -1, making the value negative. This is an implementation of Signed magnitude representation.
  If you are using One's Compliment or Two's Complement this will not give the expected value for negative values. (You are, however, clearly not using Negative Base).

Other methods

Unsigned Binary

CREATE OR ALTER FUNCTION [dbo].[UnsignedBinaryToDec] (@Binary varchar(64)) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@Binary)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3)
    SELECT SUM(SS.C * POWER(CONVERT(decimal(2,0),2),T.I-1)) AS Dec
    FROM Tally T
         CROSS APPLY (VALUES(SUBSTRING(REVERSE(@Binary),T.I,1)))SS(C)
    WHERE @Binary NOT LIKE '%[^0-1]%';
GO

This uses all the same logic as before, so doesn't require further details.

One's Complement

CREATE OR ALTER FUNCTION [dbo].[OnesComplementBinaryToDec] (@Binary varchar(64)) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@Binary)-1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3)
    SELECT 
           SUM(BW.B * POWER(CONVERT(decimal(2,0),2),T.I-1)) *
           CASE LEFT(@Binary,1) WHEN 0 THEN 1
                                WHEN 1 THEN -1
           END AS Dec           
    FROM Tally T
         CROSS APPLY (VALUES(TRY_CONVERT(bit,SUBSTRING(REVERSE(@Binary),T.I,1))))SS(C)
         CROSS APPLY (VALUES(CASE LEFT(@Binary,1) WHEN 0 THEN SS.C ELSE ~SS.C END))BW(B)
    WHERE @Binary NOT LIKE '%[^0-1]%';
GO

Much of the same here. You'll note, however, the additional CROSS APPLY. ~ is a Bitwise NOT. This means that 1 becomes 0 and 0 becomes 1, so when the first digit is a 1 (denoting the value is negative), then the Bitwise NOT is applied.

Two's Complement

CREATE OR ALTER FUNCTION [dbo].[TwosComplementBinaryToDec] (@Binary varchar(64)) 
RETURNS table
AS RETURN
    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT TOP (LEN(@Binary)-1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3)
    SELECT 
           SUM(BW.B * POWER(CONVERT(decimal(2,0),2),T.I-1)) *
           CASE LEFT(@Binary,1) WHEN 0 THEN 1
                                WHEN 1 THEN -1
           END - LEFT(@Binary,1) AS Dec
    FROM Tally T
         CROSS APPLY (VALUES(TRY_CONVERT(bit,SUBSTRING(REVERSE(@Binary),T.I,1))))SS(C)
         CROSS APPLY (VALUES(CASE LEFT(@Binary,1) WHEN 0 THEN SS.C ELSE ~SS.C END))BW(B)
    WHERE @Binary NOT LIKE '%[^0-1]%';

Identical to One's apart from that you subtract the left most digit.

Example results:

SELECT V.Binary AS B,
       U.[Dec] AS U,
       S.[Dec] AS S,
       [1s].[Dec] AS [1s],
       [2s].[Dec] AS [2s]
FROM (VALUES('0000'),('0001'),('0010'),('0011'),
            ('0100'),('0101'),('0110'),('0111'),
            ('1000'),('1001'),('1010'),('1011'),
            ('1100'),('1101'),('1110'),('1111'))V(Binary)
     CROSS APPLY dbo.UnsignedBinaryToDec(Binary) U
     CROSS APPLY dbo.SignedBinaryToDec(Binary) S
     CROSS APPLY dbo.OnesComplementBinaryToDec(Binary) [1s]
     CROSS APPLY dbo.TwosComplementBinaryToDec(Binary) [2s]
ORDER BY V.Binary;

^{* Represents -0}

CodePudding user response：

For now I have managed to create a function that turns a varchar(8) bit sequence into an integer.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION  BinarySequenceToInt
(
    -- Add the parameters for the function here
    @BinarySequence varchar(64)
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    
    DECLARE @bitIndex int = 1;
    DECLARE @SequenceLength int = LEN(@BinarySequence)
    DECLARE @powerInversionNumber int = @SequenceLength;
    DECLARE @BitSequenceNumber int = 0;
    
    WHILE @bitIndex <= @SequenceLength
        BEGIN
            IF(SUBSTRING(@BinarySequence , @bitIndex,1) = '1')
                BEGIN
                    SET @BitSequenceNumber = @BitSequenceNumber   POWER(2,@powerInversionNumber-@bitIndex);
                END
            SET @bitIndex  = 1;
        END
    return @BitSequenceNumber

END
GO

Which returns 73 if I pass it '01001000'. I know that instead of hard coding 8 I could take the length of the varchar passed so I will work on that.