转换为十八进制 create function F_int18(@num int) returns nvarchar(50) as begin if @num=0 return '0' declare @s nvarchar(50) set @s='' while @num>0 select @s=substring('0123456789ABCDEFHG',@num%18+1,1)+@s,@num=@num/18 return @s endgo ----------十八进制转换为十进制 create function F_int10(@s nvarchar(50)) returns int as begin declare @i int,@s2 nvarchar(2),@num int select @i=len(@s),@num=0 while @i>0 select @s2=substring(reverse(@s),@i,1), @num=power(18,@i-1)*(charindex(@s2,'0123456789ABCDEFHG')-1)+@num, @i=@i-1 return @num end go select dbo.F_int18(9999) select dbo.F_int10('1CF9')/**//* -------------------------------------------------- 1CF9(所影响的行数为 1 行) ----------- 9999(所影响的行数为 1 行)*/--十进制转为十六进制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-----二进制转换十进制-----------------select sum(data1)from (select substr('1101', rownum, 1) * power(2, length('1101') - rownum) data1from dualconnect by rownum <= length('1101'))-----八进制转换十进制-----------------select sum(data1)from (select substr('1101', rownum, 1) * power(8, length('1101') - rownum) data1from dualconnect by rownum <= length('1101'))
SELECT CAST(0xE1FAFFB3E614E6C2FBA74296962386B7 AS VARCHAR(8000)) --狷虫媛B枛#喎
同意,就是CONVERT(VARCHAR(8000),COL)
SELECT CAST(0xE1FAFFB3E614E6C2FBA74296962386B7 AS NVARCHAR(4000)) ?
create function F_int18(@num int)
returns nvarchar(50)
as
begin
if @num=0
return '0'
declare @s nvarchar(50)
set @s=''
while @num>0
select @s=substring('0123456789ABCDEFHG',@num%18+1,1)+@s,@num=@num/18
return @s
endgo
----------十八进制转换为十进制
create function F_int10(@s nvarchar(50))
returns int
as
begin
declare @i int,@s2 nvarchar(2),@num int
select @i=len(@s),@num=0
while @i>0
select @s2=substring(reverse(@s),@i,1),
@num=power(18,@i-1)*(charindex(@s2,'0123456789ABCDEFHG')-1)+@num,
@i=@i-1
return @num
end
go
select dbo.F_int18(9999)
select dbo.F_int10('1CF9')/**//*
--------------------------------------------------
1CF9(所影响的行数为 1 行)
-----------
9999(所影响的行数为 1 行)*/--十进制转为十六进制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-----二进制转换十进制-----------------select sum(data1)from (select substr('1101', rownum, 1) * power(2, length('1101') - rownum) data1from dualconnect by rownum <= length('1101'))-----八进制转换十进制-----------------select sum(data1)from (select substr('1101', rownum, 1) * power(8, length('1101') - rownum) data1from dualconnect by rownum <= length('1101'))
--狷虫媛B枛#喎
值是E1FAFFB3E614E6C2FBA74296962386B7,用convert肯定不行了
值是E1FAFFB3E614E6C2FBA74296962386B7,用convert肯定不行了晕,就是想保存后面的字符么?/直接用字符型字段保存不就行了,
取的时候再取RIGHT(COL,LEN(COL)-2)??
这样?
一楼icelovey兄的方法挺好的,不知道怎么用
DECLARE
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),'abc')
select @binary
select sys.fn_varbintohexstr(@binary)
-----------------------------------------------------------------------------------------------------------------
0x616263(1 行受影响)
-----------------------------------------------------------------------------------------------------------------
0x616263(1 行受影响)用 sys.fn_varbintohexstr
binary...表示
固定长度的 n 个字节二进制数据。N 必须从 1 到 8,000。存储空间大小为 n+4字节