二进制转为十进制函数:
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