--十进制转为十六进制DECLARE @binary varbinary(255), @str_return varchar(255) SELECT @binary = CONVERT(varbinary(255),29327795562177529) EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT SELECT 结果 = reverse(@str_return)/* 结果 __________________________ 008613974878369F10000011x0 */--十六进制转为十进制 CREATE FUNCTION dbo.f_hex_dec(@s varchar(16)) RETURNS bigint AS BEGIN --作者:pbsql --参数不得含'0'~'9'、'a'~'f'、'A'~'F'之外的任意字符(首尾空格除外),否则返回0 DECLARE @i int,@result bigint SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s)))) WHILE @i<LEN(@s) BEGIN IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F' BEGIN SELECT @result=0 break END SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1 END RETURN @result END GO *****************
DECLARE @varbin varbinary(max) SET @varbin = 0x1f2e3d DECLARE @bits varchar(max) DECLARE @i int, @j int, @tmp int SET @bits = NULL SET @i = 1 WHILE @i <= DATALENGTH(@varbin) BEGIN SET @tmp = CAST(SUBSTRING(@varbin, @i, 1) AS int) SET @j = 7 SET @bits = ISNULL(@bits + ' ', '') --增加结果可读性,每8位用空格隔开 WHILE @j >= 0 BEGIN SET @bits = @bits + CASE WHEN @tmp & POWER(2, @j) = 0 THEN '0' ELSE '1' END SET @j = @j - 1 END SET @i = @i + 1 END SELECT @bits
http://topic.csdn.net/u/20100528/16/f3c160a2-6d97-4e19-8f74-154d34a940d7.html
第7条。
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),29327795562177529)
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT
SELECT
结果 = reverse(@str_return)/*
结果
__________________________
008613974878369F10000011x0
*/--十六进制转为十进制
CREATE FUNCTION dbo.f_hex_dec(@s varchar(16))
RETURNS bigint
AS
BEGIN
--作者:pbsql
--参数不得含'0'~'9'、'a'~'f'、'A'~'F'之外的任意字符(首尾空格除外),否则返回0
DECLARE @i int,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@s)
BEGIN
IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
BEGIN
SELECT @result=0
break
END
SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1
END
RETURN @result
END
GO
*****************
DECLARE @varbin varbinary(max)
SET @varbin = 0x1f2e3d
DECLARE @bits varchar(max)
DECLARE @i int, @j int, @tmp int
SET @bits = NULL
SET @i = 1
WHILE @i <= DATALENGTH(@varbin)
BEGIN
SET @tmp = CAST(SUBSTRING(@varbin, @i, 1) AS int)
SET @j = 7
SET @bits = ISNULL(@bits + ' ', '') --增加结果可读性,每8位用空格隔开
WHILE @j >= 0
BEGIN
SET @bits = @bits + CASE WHEN @tmp & POWER(2, @j) = 0 THEN '0' ELSE '1' END
SET @j = @j - 1
END
SET @i = @i + 1
END
SELECT @bits