将VARCHAR类型的存储为二进制表示的数字转换为INT类型

Question

我正在处理一个SQL Server数据库，其中有一列存储的是二进制数字。

但是，这个二进制数字是以VARCHAR格式存储的。

我需要将这个VARCHAR转换为其数值表示形式，以便进行进一步的计算。

例如，我希望将字符串"0010"转换为数值2。

然而，当我尝试将字符串CAST为int时，得到的结果是10而不是2。

有没有人能提供一些关于如何在SQL中完成这种转换的建议？

Rob W · Answer

以下内联表值函数（iTVF）能够解决上述问题，正如OP在问题相关评论中进一步解释的那样。

CREATE FUNCTION dbo.UnmaskWeeks
        (@WeekMask VARCHAR(64))
/*********************************************************************************************
目的：
给定一个恰好由52个字符组成的“周掩码”，其中“1”表示“感兴趣的一周”，“0”表示不是，
并且最右侧的字符代表“第1周”。此函数将返回“感兴趣的一周”列表作为单列展示。
----------------------------------------------------------------------------------------------
使用示例：
-- 单值使用示例。
     -- 下面的例子会在名为“WeekNumber”的列中返回值51、4和1。请注意，最右侧的字符表示“第1周”。
 SELECT WeekNumber 
   FROM dbo.UnmaskWeeks ('0100000000000000000000000000000000000000000000001001')

-- 表中列的使用示例。
     -- RowPK 是唯一标识每行的列，因为对于任何给定行，如果掩码中包含多个周数，
     -- 此函数将返回多行。因此，必须使用 OUTER APPLY。
 SELECT st.RowPK, uw.WeekNumber
   FROM dbo.SomeTable st
  OUTER APPLY dbo.UnmaskWeeks (st.SomeWeekMaskColumn) uw
----------------------------------------------------------------------------------------------
程序员注释：
1. 这是一个高性能的内联表值函数（iTVF），适用于从SQL Server 2005及更高版本的所有版本。
2. 函数返回的周数按照字符串中从左到右的顺序，默认为出现顺序。换句话说，它们将是数值递减的。
   如果需要其他顺序，请在外部自行处理。
3. 如果@WeekMask长度不是确切的52个字符，则不会返回任何行。如果希望即使在掩码长度不正确的情况下RowPK也能返回NULL值，
   需要在上面示例用法部分所示那样使用OUTER APPLY，否则将不会返回任何行。
----------------------------------------------------------------------------------------------
修订历史：
Rev 00 - 2024年1月4日 - Jeff Moden
                      - 初始创建与单元测试。
参考：https://stackoverflow.com/questions/77759453/convert-a-binary-representation-of-a-number-stored-as-varchar-to-int
*********************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN WITH 
    E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1))E0(N)) --最多生成8行
   ,Tally(N) AS (--==== 这是Itzik Ben-Gan的GETNUMS()函数的一个非常小的版本
                 SELECT TOP(52)
                        N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                  FROM E1 a,E1 b --最多生成64行
                )
 SELECT WeekNumber = 53-t.N
   FROM Tally t
  WHERE LEN(@WeekMask) = 52 --如果周掩码长度不正确，则返回空结果
    AND ASCII(SUBSTRING(@WeekMask,t.N,1)) = 49 --利用ASCII提高一点性能
;
GO

David G · Answer

创建一个名为“ConvertBitStringToBigInt”的用户自定义函数，用于将1到64位的字符串转换为整数（bigint）值。该函数遵循以下逻辑：

将输入字符串填充至64位。
将每个4位值替换为相应的十六进制数字。
使用内置转换函数将得到的十六进制字符串转换为BIGINT。

函数代码如下：

-- 创建一个函数，将1-64位的二进制字符串转换为bigint值。
-- 参考来源：https://stackoverflow.com/questions/77759453/
-- 参考来源：https://stackoverflow.com/questions/58952833/
CREATE FUNCTION ConvertBitStringToBigInt(@BitString VARCHAR(65))
RETURNS BIGINT
AS
BEGIN
    -- 验证输入：非空、非空白、长度小于等于64且仅包含二进制字符。
    -- （输入参数稍大以检测过大的输入。）
    IF (
        @BitString IS NULL
        OR @BitString = ''
        OR LEN(@BitString) > 64
        OR @BitString LIKE '%[^01]%'
    )
        RETURN NULL

    -- 在左侧填充输入至64个字符
    DECLARE @PaddedString VARCHAR(100)
        = REPLICATE('0', 64 - LEN(@BitString)) + @BitString

    -- 在每个4位组之间插入分隔符
    DECLARE @DelimitedString VARCHAR(100)
        = CONCAT(
            SUBSTRING(@PaddedString,  1, 4), '_', SUBSTRING(@PaddedString,  5, 4), '_',
            SUBSTRING(@PaddedString,  9, 4), '_', SUBSTRING(@PaddedString, 13, 4), '_',
            SUBSTRING(@PaddedString, 17, 4), '_', SUBSTRING(@PaddedString, 21, 4), '_',
            SUBSTRING(@PaddedString, 25, 4), '_', SUBSTRING(@PaddedString, 29, 4), '_',
            SUBSTRING(@PaddedString, 33, 4), '_', SUBSTRING(@PaddedString, 37, 4), '_',
            SUBSTRING(@PaddedString, 41, 4), '_', SUBSTRING(@PaddedString, 45, 4), '_',
            SUBSTRING(@PaddedString, 49, 4), '_', SUBSTRING(@PaddedString, 53, 4), '_',
            SUBSTRING(@PaddedString, 57, 4), '_', SUBSTRING(@PaddedString, 61, 4)
        )

    -- 将每个4位组转换为十六进制数字并移除分隔符
    DECLARE @HexSTring VARCHAR(100) =
        REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(
            @DelimitedString
            , '0000', '0'), '0001', '1'), '0010', '2'), '0011', '3')
            , '0100', '4'), '0101', '5'), '0110', '6'), '0111', '7')
            , '1000', '8'), '1001', '9'), '1010', 'a'), '1011', 'b')
            , '1100', 'c'), '1101', 'd'), '1110', 'e'), '1111', 'f')
            , '_', '')

    -- 将十六进制转换为BINARY（原始字节串）类型，然后转换为BIGINT
    RETURN CONVERT(BIGINT, CONVERT(VARBINARY(8), @HexSTring, 2))
END

示例结果：
以下是您提供的表格内容以Markdown格式呈现：

| BitString                               | BigIntResult   | Binary8                           |
| --------------------------------------- | -------------- | --------------------------------- |
| 0010                                    | 2              | 0x0000000000000002                 |
| 0000000000000000000000000000000000000000000000000010 | 2              | 0x0000000000000002                 |
| 0100000000000000000000000000000000000000000000001001 | 1125899906842633 | 0x0004000000000009                 |
| 0                                       | 0              | 0x0000000000000000                 |
| 1                                       | 1              | 0x0000000000000001                 |
| 11111111                                | 255            | 0x00000000000000FF                 |
| 1111111111111111                        | 65535          | 0x000000000000FFFF                 |
| 01111111111111111111111111111111        | 2147483647     | 0x000000007FFFFFFF                 |
| 11111111111111111111111111111111        | 4294967295     | 0x00000000FFFFFFFF                 |
| 0111111111111111111111111111111111111111111111111111111111111111 | 9223372036854775807 | 0x7FFFFFFFFFFFFFFF                 |
| 1111111111111111111111111111111111111111111111111111111111111111 | -1             | 0xFFFFFFFFFFFFFFFF                 |
| 1000000000000000000000000000000000000000000000000000000000000000 | -9223372036854775808 | 0x8000000000000000                 |
| 1111111011011100101110101001100001110110010101000011001000010000 | -81985529216486896 | 0xFEDCBA9876543210                 |
| 11111111111111111111111111111111111111111111111111111111111111111 | null           | null                              |
| 012                                     | null           | null                              |
|                                         | null           | null                              |
| 00000110010110110001110101101110100    | 853076852      | 0x0000000032D8EB74                 |
| 11000111010110111010                    | 816570         | 0x00000000000C75BA                 |
| 00000000000011000111010110111010        | 816570         | 0x00000000000C75BA                 |
| 11111111111100111000101001000110        | 4294150726     | 0x00000000FFF38A46                 |
| 111111111111111111111111111111111111111111111100111000101001000110 | -816570        | 0xFFFFFFFFFFF38A46                 |

在此db<>fiddle示例中可查看该函数的实际效果和更多示例结果。