我有一个表示二进制数的字符串 001011
,我想将其转换为 binary
或 varbinary
格式(即 0xB
)。在MS SQL Server中怎么做?
我有一个表示二进制数的字符串 001011
,我想将其转换为 binary
或 varbinary
格式(即 0xB
)。在MS SQL Server中怎么做?
CREATE FUNCTION dbo.fnConvertToBinary
(@str VARCHAR(32), @base TINYINT)
RETURNS VARBINARY(32)
AS
BEGIN
DECLARE @bytes INT = 0
DECLARE @i INT = 0
DECLARE @l INT = LEN(@str)
IF (@base = 2)
BEGIN
WHILE @i <= @l
BEGIN
SET @bytes = @bytes * 2 + CAST(SUBSTRING(@str, @i, 1) AS TINYINT)
SET @i = @i + 1
END
END
IF (@base = 8)
BEGIN
WHILE @i <= @l
BEGIN
SET @bytes = (CAST(SUBSTRING(@str, @i, 1) AS TINYINT)
* POWER(8, @l-@i))
+ @bytes
SET @i = @i + 1
END
END
IF (@base = 10)
SET @bytes = CAST(@str AS INT)
IF (@base = 16)
BEGIN
SET @i = @l
WHILE @i > 0
BEGIN
DECLARE @bit INT = (CASE SUBSTRING(@str, @i, 1)
WHEN '0' THEN 0 WHEN '1' THEN 1
WHEN '2' THEN 2 WHEN '3' THEN 3
WHEN '4' THEN 4 WHEN '5' THEN 5
WHEN '6' THEN 6 WHEN '7' THEN 7
WHEN '8' THEN 8 WHEN '9' THEN 9
WHEN 'A' THEN 10 WHEN 'B' THEN 11
WHEN 'C' THEN 12 WHEN 'D' THEN 13
WHEN 'E' THEN 14 WHEN 'F' THEN 15 END)
SET @bytes = @bit * POWER(16, @l-@i) + @bytes
SET @i = @i - 1
END
END
RETURN CAST(@bytes AS VARBINARY(32))
END
GO
更新:没有考虑到字符串中的“二进制”部分。
一种方法是使用数字表:
with c(a) as (
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
)
select cast( sum ( substring('001011', a, 1) * power(2, len('001011') - a) ) as varbinary(10)) from c
cast('101100' as int)
。 - user166390001011
作为整数是11,而不是1011。这将产生0x0...03F3,而不是0x0...B。 - Aaron Bertrand这是我针对最多六位数字的字符串的解决方案:
select cast((POWER(2, 5)*(ascii(substring(val, 6-5, 1)) - ASCII('0')) +
POWER(2, 4)*(ascii(substring(val, 6-4, 1)) - ASCII('0')) +
POWER(2, 3)*(ascii(substring(val, 6-3, 1)) - ASCII('0')) +
POWER(2, 2)*(ascii(substring(val, 6-2, 1)) - ASCII('0')) +
POWER(2, 1)*(ascii(substring(val, 6-1, 1)) - ASCII('0')) +
POWER(2, 0)*(ascii(substring(val, 6-0, 1)) - ASCII('0'))
) as BINARY(2))
from (select '001011' as val) t
我这样写是为了让如何将其推广到更长的字符串显而易见。如果超过15个字符,那么增加binary()
的大小。
CREATE FUNCTION [dbo].[BinaryToDecimal]
(
@Input varchar(255)
)
RETURNS bigint
AS
BEGIN
DECLARE @Cnt tinyint = 1
DECLARE @Len tinyint = LEN(@Input)
DECLARE @Output bigint = CAST(SUBSTRING(@Input, @Len, 1) AS bigint)
WHILE(@Cnt < @Len) BEGIN
SET @Output = @Output + POWER(CAST(SUBSTRING(@Input, @Len - @Cnt, 1) * 2 AS bigint), @Cnt)
SET @Cnt = @Cnt + 1
END
RETURN @Output
END
SELECT CAST(dbo.BinaryToDecimal('001011') AS binary(1))