我想知道在SQL中是否有一种简单的方法可以将整数转换为二进制表示形式,然后将其存储为varchar.
例如,5将转换为"101"并存储为varchar.
以下可以编码为函数.您需要修剪前导零以满足您的问题要求.
declare @intvalue int set @intvalue=5 declare @vsresult varchar(64) declare @inti int select @inti = 64, @vsresult = '' while @inti>0 begin select @vsresult=convert(char(1), @intvalue % 2)+@vsresult select @intvalue = convert(int, (@intvalue / 2)), @inti=@inti-1 end select @vsresult
实际上,使用普通的旧SQL非常简单.只需使用按位AND.我有点惊讶的是,没有一个简单的解决方案在线发布(没有调用UDF).在我的情况下,我真的想检查位是打开还是关闭(数据来自dotnet eNums).
因此,这里有一个例子,它将单独和一起给你 - 位值和二进制字符串(大联盟只是一种产生数字的hacky方式,可以在DBs上工作:
select t.Number , cast(t.Number & 64 as bit) as bit7 , cast(t.Number & 32 as bit) as bit6 , cast(t.Number & 16 as bit) as bit5 , cast(t.Number & 8 as bit) as bit4 , cast(t.Number & 4 as bit) as bit3 , cast(t.Number & 2 as bit) as bit2 ,cast(t.Number & 1 as bit) as bit1 , cast(cast(t.Number & 64 as bit) as CHAR(1)) +cast( cast(t.Number & 32 as bit) as CHAR(1)) +cast( cast(t.Number & 16 as bit) as CHAR(1)) +cast( cast(t.Number & 8 as bit) as CHAR(1)) +cast( cast(t.Number & 4 as bit) as CHAR(1)) +cast( cast(t.Number & 2 as bit) as CHAR(1)) +cast(cast(t.Number & 1 as bit) as CHAR(1)) as binary_string --to explicitly answer the question, on MSSQL without using REGEXP (which would make it simple) ,SUBSTRING(cast(cast(t.Number & 64 as bit) as CHAR(1)) +cast( cast(t.Number & 32 as bit) as CHAR(1)) +cast( cast(t.Number & 16 as bit) as CHAR(1)) +cast( cast(t.Number & 8 as bit) as CHAR(1)) +cast( cast(t.Number & 4 as bit) as CHAR(1)) +cast( cast(t.Number & 2 as bit) as CHAR(1)) +cast(cast(t.Number & 1 as bit) as CHAR(1)) , PATINDEX('%1%', cast(cast(t.Number & 64 as bit) as CHAR(1)) +cast( cast(t.Number & 32 as bit) as CHAR(1)) +cast( cast(t.Number & 16 as bit) as CHAR(1)) +cast( cast(t.Number & 8 as bit) as CHAR(1)) +cast( cast(t.Number & 4 as bit) as CHAR(1)) +cast( cast(t.Number & 2 as bit) as CHAR(1)) +cast(cast(t.Number & 1 as bit) as CHAR(1) ) ) ,99) from (select 1 as Number union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10) as t
产生这个结果:
num bit7 bit6 bit5 bit4 bit3 bit2 bit1 binary_string binary_string_trimmed 1 0 0 0 0 0 0 1 0000001 1 2 0 0 0 0 0 1 0 0000010 10 3 0 0 0 0 0 1 1 0000011 11 4 0 0 0 1 0 0 0 0000100 100 5 0 0 0 0 1 0 1 0000101 101 6 0 0 0 0 1 1 0 0000110 110 7 0 0 0 0 1 1 1 0000111 111 8 0 0 0 1 0 0 0 0001000 1000 9 0 0 0 1 0 0 1 0001001 1001 10 0 0 0 1 0 1 0 0001010 1010
这是一个通用的基本转换器
http://dpatrickcaldwell.blogspot.com/2009/05/converting-decimal-to-hexadecimal-with.html
你可以做
select reverse(dbo.ConvertToBase(5, 2)) -- 101