有沒有一種快速的方法可以將varchar(8)二進制序列轉換為T-SQL中的整數？-有解無憂

我有一個DECLARE @binarySequence varchar(8) = '00000000';. 我使用 STUFF 手動更改每個索引中的位值。該代碼與此問題無關。一旦我更改了所有必要的位，然后SELECT @binarySequence;回傳'01001001'. 有沒有辦法讓我改變它，以便它回傳該二進制序列的十進制表示（73）而不回圈每個字符？歡迎所有建議以及任何答案/問題謝謝您的時間！

uj5u.com熱心網友回復：

有符號幅度表示二進制檔案

這是我幾年前寫的。它使用一個 Tally 將值拆分為各個字符，然后將每個值聚合到它們的相關位置POWER以獲得最終結果：

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

這是如何作業的

上面是一個行內表值函式。這意味著它回傳一個資料集，而不是一個標量值，并且您在FROM. 這樣做的原因是多行標量函式的性能可能很差。在 SQL Server 2019 中，它可以行內用戶定義的標量函式，但是，我不確定這樣的查詢是否會。因此，當具有基于集合的邏輯的 iTVF 將表現出色時，我不想冒險使用它。

讓我們將其分解為幾個步驟：

理貨

您將看到的查詢的第一部分是 2 個公用表運算式 (CTE)；N和Tally。N字面上只包含 4 行的值NULL。為什么是4？我稍后會解釋。為什么NULL？好吧，它可以是任意值，我只是使用NULL它，因為它的值沒有意義。

接下來我們有 CTE Tally。首先，您會注意到它參考N了FROM3 次；這意味著它N被CROSS JOINed （使用舊的 ANSI-89 語法，是的）自身 3 次，導致（最多）64 行4^3 = 64，，這是（你會注意到）引數的長度，varchar(64)。這就是我使用 4 個NULL值的原因。

在SELECTI 中將要回傳的行數限制為varchar值的長度-1。-1因為二進制值是有符號的，所以字串中的第一個數字不會用于確定聚合值（稍后完成），而是表示該值是正數還是負數。

最后ROW_NUMBER，不出所料，我們給每一行一個遞增的數字，從1. (SELECT NULL)在ORDER BY這里，因為我們（再次）需要一些任意值。

如果我們停在這里，你的價值，這將導致含7行，與值資料集1來7。您可以使用以下內容進行測驗

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;

外部選擇

我們先去FROM第二個。顯然FROM Tally從Tally我們剛剛討論過的 CTE 回傳行。接下來我們有CROSS APPLY一個VALUES表結構。這將varchar在單獨的行上回傳每個單獨的字符。請注意，我們REVERSE的最低值是在數字右側。如果您要從您的值回傳結果，您FROM最終會得到以下資料集：

一世	C
1	1
2	0
3	0
4	1
5	0
6	0
7	1

注意
REVERSE不在我最初的解決方案中，不知道我是如何錯過的，但由于價值是回文，我們“幸運”了。

在WHERE那里可以停止查詢嘗試處理任何無效值。例如，如果您要輸入'01001a01'或'013240101'函式不會回傳結果。

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;

Binary	Unsigned	Signed	One's	Two's
0000	0	0	0	0
0001	1	1	1	1
0010	2	2	2	2
0011	3	3	3	3
0100	4	4	4	4
0101	5	5	5	5
0110	6	6	6	6
0111	7	7	7	7
1000	8	0*	-7	-8
1001	9	-1	-6	-7
1010	10	-2	-5	-6
1011	11	-3	-4	-5
1100	12	-4	-3	-4
1101	13	-5	-2	-3
1110	14	-6	-1	-2
1111	15	-7	0*	-1

^{* Represents -0}

uj5u.com熱心網友回復：

現在我已經設法創建了一個將 varchar(8) 位序列轉換為整數的函式。

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

如果我通過它'01001000'，它會回傳 73。我知道代替硬編碼 8，我可以獲取傳遞的 varchar 的長度，所以我會繼續努力。

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/405448.html

標籤：

上一篇：為什么這個CUDA程式輸出左元素為0？

下一篇：插入catch塊導致錯誤：當前事務無法提交，并且無法支持寫入日志檔案的操作