if object_ID('ta') IS NOT NULL DROP TABLE ta
go
create table ta(ida int,name varchar(11) )
go
insert ta select
15867707, 'A1'union all select
15867708, 'A2'union all select
15867710, 'A3' union all select
15867711, 'A4'
if object_ID('f_int_hex') IS NOT NULL DROP function f_int_hex
gocreate function dbo.f_int_hex(@num int,@len int)
--@num 为要转换成16进制的10进制数,@len为转换后占用的位数
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
while len(@result)<@len
select @result=substring('0123456789ABCDEF',@num%16+1,1)+@result,@num=@num/16
return(@result)
end
go alter table ta add result varchar(30)
go
update ta
set result=dbo.f_int_hex(ida,10) ---- 这个10 自己设置select * from ta ida name result
----------- ----------- ------------------------------
15867707 A1 0000F21F3B
15867708 A2 0000F21F3C
15867710 A3 0000F21F3E
15867711 A4 0000F21F3F(4 行受影响) -----二进制------互相转换-----------
-----八进制-------互相转换----------
-----十六进-------互相转换----------
go
create table ta(ida int,name varchar(11) )
go
insert ta select
15867707, 'A1'union all select
15867708, 'A2'union all select
15867710, 'A3' union all select
15867711, 'A4'
if object_ID('f_int_hex') IS NOT NULL DROP function f_int_hex
gocreate function dbo.f_int_hex(@num int,@len int)
--@num 为要转换成16进制的10进制数,@len为转换后占用的位数
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
while len(@result)<@len
select @result=substring('0123456789ABCDEF',@num%16+1,1)+@result,@num=@num/16
return(@result)
end
go alter table ta add result varchar(30)
go
update ta
set result=dbo.f_int_hex(ida,10) ---- 这个10 自己设置select * from ta ida name result
----------- ----------- ------------------------------
15867707 A1 0000F21F3B
15867708 A2 0000F21F3C
15867710 A3 0000F21F3E
15867711 A4 0000F21F3F(4 行受影响) -----二进制------互相转换-----------
-----八进制-------互相转换----------
-----十六进-------互相转换----------
@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
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 行)*/
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 行)*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/roy_88/archive/2007/11/13/1882106.aspx
十进制转二进制和十六的两个函数
A.调用测试:
Update MySheet1 set 二进制= dbo.inttobit(十进制)
Update MySheet2 set 十六进制= dbo.inttohex(十进制)
B.函数内容
--十进制转二进制函数
CREATE FUNCTION dbo.inttobit (@number int)
returns varchar(100)
as
BEGIN
DECLARE @i int
DECLARE @j float
DECLARE @m int
DECLARE @OUT1 varCHAR(1)
DECLARE @OUT2 varchar(20)
SET @i=@number
set @out2=' '
WHILE @i>=1
BEGIN
SET @j=@i/2
SET @m=@i%2
SET @i=floor(@j)
SET @OUT1=cast(@m as char(1))
SET @OUT2=@OUT1+@OUT2
END
RETURN @OUT2
END --十进制转十六进制函数
CREATE function dbo.inttohex(@i int)
returns varchar(15)
begin
--declare @i int
--set @i=11259375
declare @r varchar(10)
set @r=''
while @i/16>0
begin
set @r=
(case
when (@i % 16)<=9 then convert(varchar(1),@i % 16)
when (@i % 16)=10 then 'A'
when (@i % 16)=11 then 'B'
when (@i % 16)=12 then 'C'
when (@i % 16)=13 then 'D'
when (@i % 16)=14 then 'E'
when (@i % 16)=15 then 'F'
end)
+@r
--select @r,@i
set @i=@i/16
end
--select @r,@i
if @i>0
set @r=(case
when (@i % 16)<=9 then convert(varchar(1),@i % 16)
when (@i % 16)=10 then 'A'
when (@i % 16)=11 then 'B'
when (@i % 16)=12 then 'C'
when (@i % 16)=13 then 'D'
when (@i % 16)=14 then 'E'
when (@i % 16)=15 then 'F'
end)+@r
-- select @r
return @r
end本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/xqf222/archive/2008/04/25/2327063.aspx
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'))
Create Table #ConvertOrderSerial
(orderSerial int)
DECLARE @StrSerial int
DECLARE @OrderSerial int
select @StrSerial=result2 from ta --Ltrim(Rtrim(Right(Left(Ltrim(Rtrim(@sss_Recv)),6),2)))
exec('insert #ConvertOrderSerial select Convert(int,0x'+@StrSerial+')')
select @OrderSerial=orderserial from #ConvertOrderSerial
print @OrderSerial各位看看这方法如何!
DECLARE @OrderSerial int Create Table #ConvertOrderSerial
(orderSerial int)
select @StrSerial=result3 from ta--Ltrim(Rtrim(Right(Left(Ltrim(Rtrim(@sss_Recv)),6),2)))
exec('insert #ConvertOrderSerial select Convert(int,0x'+@StrSerial+')')
select orderserial from #ConvertOrderSerial
drop table #ConvertOrderSerial
print @OrderSerial