找到一个10转16的.--创建16进制转换字符串函数
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)
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)
解决方案 »
- 关于字符串相加的问题:由于null加上任何字符串都为null
- ???
- 代码中有部分看不懂,帮忙解释下,谢谢
- 【!续刚才那个简单的日期比较问题!】
- 这样的错误:Error: 2809, Severity: 18, State: 1是什么回事??
- 在远程链接ms sql2005数据库空间时,有链接超时的提示.我在"工具"-"选择"中把超时时间重新设置后.还是提示超时.请各位高手指点一下.
- 请教:怎么样建立一个满足如下要求的表??(详细见内)
- 用VB的DAO连SQL SERVER数据库可以吗?
- 请求数据库专家。
- 获得本机ip地址的函数有哪些?
- 疑难杂症就高手啊
- 在查询分析器中可以执行 EXEC master.dbo.xp_cmdshell 'd:\ab\p.exe beep' 为什么将它移到触发器中却不能执行?
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
--------------------
兄台。有个错误
将 expression 转换为数据类型 int 时发生算术溢出错误。
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)*cast(POWER(16,@i) as bigint),@i=@i+1
END
RETURN @result
END
GO
select dbo.f_hex_dec('B24147FE')godrop function f_hex_dec
/*
--------------------
2990622718(所影响的行数为 1 行)*/
create function dbo.f_int2hex(@num bigint)
returns varchar(100)
as
begin
declare @re varchar(100)
set @re=''
while @num> 0
select @re=substring('0123456789ABCDEF',@num%16+1,1)+@re
,@num=@num/16
return(@re)
end
go
select dbo.f_int2hex(766) --16进制转10进制
declare @a varbinary(200)
set @a=0x2fe
select cast(@a as bigint)