SQL SERVER中实现十六进制转换为字符的两种方法(一)

前几天同事遇到一客户有这样的需求：

客户给一段范围 比如0000~FFFF ，要求产生一组十六进制序列号 比如逢二进一 就要得到0002，0004，0006。。这一系列序号，最终要把这组序号加上一定的字符型prefix，存储到DB里去，前提是要求所有这一切在SQL后台处理

问我sql中有没有直接将十六进制直接转换为字符的函数？

查了查sql的book online,sql server中对十六进制的表示方法为在十六进制前加上0x,现在的问题就是如何将十六进制的前置符去掉的问题，比如：0xFFFF,要变为FFFF,而sql server中是没有这样的内部函数的，怎么办呢？想了想，大概有两种方法：

1.用sql server2000中的自定义函数去实现

   自定义函数功能强大,应该可以实现这个功能

2.通过sql server中的sp_OACreate等存储过程去调用自己写的dll

   大致想法是这样的：在VB中能很容易实现将十六进制转化为字符，比如用hex$函数即可实现，现在只要自己写一个dll，然后在sql中通过sp_oacreate存储过程去调用即可

对于第一种方法，查了一下资料，发现book online中就有现成的例子可实现这个功能，真是天助我也，看来查资料也是一种很重要的能力：），存储过程如下：

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(255),
    @hexvalue varchar(255) OUTPUT
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ”–这个是前缀字符，可定义为自己需要的字符，比如SN,0X
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = ‘0123456789abcdef’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

GO

使用时只要在sql或存储过程中这样调用即可：

declare @outValue varchar(255)

exec sp_hexadecimal 25,@outvalue output

print @outvalue–这就是你所要的结果

将其改成自定义函数，如下：

CREATE FUNCTION dbo.fn_hexadecimal (@binvalue varbinary(2))
RETURNS varchar(10)
AS 
BEGIN
DECLARE @charvalue varchar(10)
DECLARE @hexstring char(16)
DECLARE @i int
DECLARE @length int
SELECT @charvalue = ”
SELECT @hexstring = ‘0123456789ABCDEF’
SELECT @i=1
SELECT @length=DATALENGTH(@binvalue)
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
RETURN @charvalue
END

注：

1.@binvalue的长度对输出结果很重要，如果你要输出’FFFF’四位的格式，则定义成@binvalue varbinary(2)

2.RETURNS varchar(10)，@charvalue–这个宽度不能小于@binvalue宽度的两倍，否则会被截断

使用时跟使用sql内部函数一样

—-待续—-

相关连接：

SQL SERVER中实现十六进制转换为字符的两种方法(二)

SQL SERVER中实现十六进制转换为字符的两种方法(三)