binary和varbinary数据类型.docx

binary和varbinary数据类型1.binary类型需要指定字节数,默认是30, 也就是表示最多可以存30个字节整数(240bit, 很大的数), 在presentation上是60个Hex字符串如declare @bi binary(8)set @bi=12select @bi--output 0x000000000000000C2.binary不接收字符串, 但可以先把字符串转为binarydeclare @bi binary(8)set @bi=cast(12 as binary(8))select @bi--output 0x3132000000000000是字符的ascii代码, 补零的方向与上面不同3.varbinary就是去掉上面的那些00, 和varchar去掉空格 一样T-SQL: 关于 Varbinary(Hex,Int) 与 Varchar(HexString) 之间的数据类型转换 我以前只是用存储过程简单地实现了: 16进制数的字符串表达 转变为 真正的16进制数: 例如: 0x5f 转换成 16进制的 0x5f declare @ varbinary(8000) declare @s varchar(100) declare @sql nvarchar(100) set @s = 0xFFFF set @sql = Nset @ = + cast(@s as nvarchar(100)) exec SP_EXECUTESQL @sql ,N@ varbinary(8000) out, @ out select @ 该方法属于投机取巧,只实现了 HexString to Hex 的单向转换,而且不能用 自定义函数(udf) 实现! 2.HexToInt函数实现:-- By Aaron West, 5/4/2005 -- This version allows negative numbers --return null if input is not a valid hex stringCREATE FUNCTION dbo.HexToINT ( ?@Value VARCHAR(8) ) RETURNS INT AS BEGIN ?if @Value LIKE %[^0-9A-Fa-f]% RETURN NULL ?DECLARE @I BIGINT ?SET @I = CAST(CAST(RIGHT(UPPER+ @Value),8) AS BINARY(8)) AS BIGINT) - CAST(0x3030303030303030 AS BIGINT) ?SET @I=@I-((@I/16)CAST(0x0101010101010101 AS BIGINT))*7 ?RETURN CAST(CAST( (@I15) +((@I/16)240) +((@I/256)3840) +((@I/4096)61440) +((@I/65536)983040) +((@I/1048576 +((@I251658240) +(@I/CAST(0x0100000000000000 AS BIGINT)*268435456) ? AS BINARY(4))AS INT) END GO ?SELECT ? dbo.HexToINT(0ABC) , ? dbo.HexToINT(7FFF) , ? dbo.HexToINT(0FFF) , ? dbo.HexToINT(0) ? ?AS Zero, ? dbo.HexToINT(7FFFFFFF) AS MaxValue, dbo.HexToINT AS MaxNeg, dbo.HexToINT(FFFFFFFF) AS NegOne ?3. varbinary to hexstr函数实现create function dbo.ufn_vbintohexstr ( ?@vbin_in varbinary(256) ) returns varchar(514) as Begin ?declare @x bigint ?declare @len int ?declare @loop int ?declare @value varbinary(514) ?set @value = 0x ?set @loop = 1 ?set @len = datalength(@vbin_in) ?if (@len 1) 0 set @vbin_in = 0x00 + @vbin_in ?if (@len 3) 3 set @vbin_in = 0x0000 + @vb