sql 数字进制转换

  二进制转为十进制函数：

CREATE FUNCTION Bin2Dec(@StrBin VARCHAR(32))

RETURNS INT                        ---- 只支持最多 31 位长二进制字符串的解析

AS

BEGIN

    DECLARE @DecValue AS INT       -- 十进制值

    DECLARE @BinLen AS TINYINT     -- 二进制字符串长度

    DECLARE @Index AS TINYINT      -- 处理二进制字符串长度的索引

    DECLARE @CurrBit AS CHAR(1)    -- 当前在处理哪一位

    SET @BinLen = LEN(@StrBin)

    SET @DecValue = 0

    SET @Index = 0

    WHILE @Index < @BinLen

    BEGIN

        SET @Index = @Index + 1

        SET @CurrBit = CAST(SUBSTRING(@StrBin, @Index, 1) AS CHAR(1))

            IF (@CurrBit <> '0' AND @CurrBit <> '1')   -- 出现非法字符 , 返回 -1  

            BEGIN

                SET @DecValue = -1

                BREAK

            END

        ELSE    

            BEGIN

                SET @DecValue = @DecValue * 2

                IF(@CurrBit = '1')

                    BEGIN

                        SET @DecValue = @DecValue + POWER(2, 0)

                    END

            END     

    END

    RETURN @DecValue

END

十进制转为二进制  

CREATE FUNCTION Dec2Bin(@Dec BigINT, @StrLen TINYINT) --7125411734408899 51969950402343442

RETURNS VARCHAR(63) -- INT型,4字节,正数转为二进制字符串最多31位

AS

BEGIN

    DECLARE @BinStr AS VARCHAR(64)      -- 二进制表示的字符串

    DECLARE @Div2 AS BIGINT                -- 商

    DECLARE @Mod2 AS BIGINT                -- 模/余数

    Declare @FirStr AS VARCHAR(32)

    Declare @SecStr As VARCHAR(32)

    Declare @FirInt AS int

    Declare @SecInt As int

    Declare @MobileSec int

    Declare @MobileZip varchar(20)

    IF @Dec < 0

        RETURN 'NULL'              -- 不支持负数的转换

    SET @Div2 = @Dec / 2 

    SET @Mod2 = @Dec % 2 

    SET @BinStr = ''

    WHILE @Div2 <> 0

    BEGIN

        SET @BinStr = CAST(@Mod2 AS CHAR(1)) + @BinStr

        SET @Dec = @Dec / 2

        SET @Div2 = @Dec / 2

        SET @Mod2 = @Dec % 2

    END

   

    SET @BinStr = CAST(@Mod2 AS CHAR(1)) + @BinStr -- 至此,已完成十进制到二进制的转换

    IF @StrLen > LEN(@BinStr)    -- 如果用户指定的长度大于实际长度，则需要左边补0

        BEGIN

            IF @StrLen > 64    -- 返回的长度，最长为32

                SET @StrLen = 64

            DECLARE @ZeroStr VARCHAR(64)    -- 需要补充的"0000..."

            DECLARE @OffsetLen TINYINT       -- 需要补充几个"0"

            SET @ZeroStr = ''

            SET @OffsetLen = @StrLen - LEN(@BinStr)

 

            WHILE @OffsetLen > 0

            BEGIN

                SET @ZeroStr = @ZeroStr + '0'

                SET @OffsetLen = @OffsetLen - 1

            END

            SET @BinStr = @ZeroStr + @BinStr

            SET @FirStr = SUBSTRING(@BinStr,0,33)

            SET @SecStr = SUBSTRING(@BinStr,33,64)

            select @FirInt = dbo.Bin2Dec(@FirStr)

            select @SecInt = dbo.Bin2Dec(@SecStr)

            SET @MobileSec = @FirInt^@SecInt

            select @MobileZip = rtrim(mose_zip) from dbo.[tb_MobileSect]

                                    where mose_mobile_sect = left(cast(@MobileSec as varchar(20)),9)

        END

    RETURN @MobileZip

END