CREATE FUNCTION dbo.f_hex_dec(@s varchar(16))
RETURNS bigint
AS
BEGIN
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
RETURNS bigint
AS
BEGIN
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
解决方案 »
- 查询范围有OR时,无法实现翻页排序
- sql server在远程还原时报错,请大家帮忙
- 急求...数据库查询 连接中文 出现乱码??????
- SQL Server2000 未公开的存储过程(1)
- 80分求一個SQL語句
- 请教一下这个存储过程问题 大家帮忙来看看哇!!!
- sqlserver中四种服务:Distributed Transaction Coordinator,Microsoft Search,Sql Server,Sql Server Agent 前面两种分别起什么作用?
- 导数据问题,急!!!
- 正在做C/S,服务器为sql server2000能否让用户不安装sql server2000,能否像asa 6.0一样。
- SQL SERVER 7.0数据库同步复制出错处理?万分紧急,请速援手,谢谢!!!!
- 如何处理(1,3,5,9)这样的字段?
- 用户有8万人,每天会使用一次,要保存5年数据,谁知道如何选择服务器,用sql server2005能否处理
CREATE function fn_hex_to_char (
@x varbinary(100), -- binary hex value
@l int -- number of bytes
) returns varchar(200)
as
-- Written by: Gregory A. Larsen
-- Date: May 25, 2004
-- Description: This function will take any binary value and return
-- the hex value as a character representation.
-- In order to use this function you need to pass the
-- binary hex value and the number of bytes you want to
-- convert.
begin
declare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s varchar(100)
declare @h varchar(100)
declare @j int
set @j = 0
set @h = ''
-- process all bytes
while @j < @l
begin
set @j= @j + 1
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s = cast(substring(@digits,@i%16+1,1) as char(1)) + @s
-- build string of hex characters
set @h = @h + @s
end
return(@h)
end
go
--调用
select dbo.fn_hex_to_char(convert(varbinary,100),4)