在sql server中,希望将一字段中的16进制字符双双转换成asc字符后相加,即
declare @s varchar(100)
set @s='ffffffffffff'
希望得到的是转换后的字符串:ÿÿÿÿÿÿ,即ff->ÿ,在操作上采用循环的方法进行相加,但得不到希望的结果,方法如下:declare @s varchar(100),@s1 nchar(100),@s2 nchar(1),@s3 nchar(1),@s4 nchar(1),@s5 nchar(1),@s6 nchar(1),@i int
declare @sx nchar(6);
set @s='ffffffffffff';
set @i=0
set @s1=''
--set @s1=NCHAR(dbo.hextoint(substring(@s,1,2)));
--set @s2=NCHAR(dbo.hextoint(substring(@s,3,2)));
--set @s3=NCHAR(dbo.hextoint(substring(@s,5,2)));
--set @s4=NCHAR(dbo.hextoint(substring(@s,7,2)));
--set @s5=NCHAR(dbo.hextoint(substring(@s,9,2)));
--set @s6=NCHAR(dbo.hextoint(substring(@s,11,2)));
--set @sx=convert(nchar(1),@s1)+@s2+@s3+@s4+@s5+@s6
--select @sxwhile @i<6
begin
set @s1 = convert(nchar(1),@s1)+convert(nchar(1),NCHAR(dbo.hextoint(substring(@s,@i+1,2))));
set @i=@i+2
end
select @s1
-- 结果为 ÿÿ,而不是所要的:ÿÿÿÿÿÿ-- 借用的函数为:
create function [dbo].[hextoint](@h varchar(8))
returns bigint
as
begin
declare @r bigint
set @r=0
declare @i bigint
set @i=1
while @i<=len(@h)
begin
set @r=@r+
convert(int,
(
case
when substring(@h,@i,1)<='9' then substring(@h,@i,1)
when substring(@h,@i,1)<='A' then '10'
when substring(@h,@i,1)<='B' then '11'
when substring(@h,@i,1)<='C' then '12'
when substring(@h,@i,1)<='D' then '13'
when substring(@h,@i,1)<='E' then '14'
when substring(@h,@i,1)<='F' then '15'
end
))
*power(16,len(@h)-@i)
set @i=@i+1
end
return @r
end
希望高手指点迷津,谢谢!
declare @s varchar(100)
set @s='ffffffffffff'
希望得到的是转换后的字符串:ÿÿÿÿÿÿ,即ff->ÿ,在操作上采用循环的方法进行相加,但得不到希望的结果,方法如下:declare @s varchar(100),@s1 nchar(100),@s2 nchar(1),@s3 nchar(1),@s4 nchar(1),@s5 nchar(1),@s6 nchar(1),@i int
declare @sx nchar(6);
set @s='ffffffffffff';
set @i=0
set @s1=''
--set @s1=NCHAR(dbo.hextoint(substring(@s,1,2)));
--set @s2=NCHAR(dbo.hextoint(substring(@s,3,2)));
--set @s3=NCHAR(dbo.hextoint(substring(@s,5,2)));
--set @s4=NCHAR(dbo.hextoint(substring(@s,7,2)));
--set @s5=NCHAR(dbo.hextoint(substring(@s,9,2)));
--set @s6=NCHAR(dbo.hextoint(substring(@s,11,2)));
--set @sx=convert(nchar(1),@s1)+@s2+@s3+@s4+@s5+@s6
--select @sxwhile @i<6
begin
set @s1 = convert(nchar(1),@s1)+convert(nchar(1),NCHAR(dbo.hextoint(substring(@s,@i+1,2))));
set @i=@i+2
end
select @s1
-- 结果为 ÿÿ,而不是所要的:ÿÿÿÿÿÿ-- 借用的函数为:
create function [dbo].[hextoint](@h varchar(8))
returns bigint
as
begin
declare @r bigint
set @r=0
declare @i bigint
set @i=1
while @i<=len(@h)
begin
set @r=@r+
convert(int,
(
case
when substring(@h,@i,1)<='9' then substring(@h,@i,1)
when substring(@h,@i,1)<='A' then '10'
when substring(@h,@i,1)<='B' then '11'
when substring(@h,@i,1)<='C' then '12'
when substring(@h,@i,1)<='D' then '13'
when substring(@h,@i,1)<='E' then '14'
when substring(@h,@i,1)<='F' then '15'
end
))
*power(16,len(@h)-@i)
set @i=@i+1
end
return @r
end
希望高手指点迷津,谢谢!
set @s='ff00ff00ff00ff00ff00'
exec('select cast(cast(0x'+@s+' as varbinary) as nchar)') /*------------------------------
ÿÿÿÿÿ*/
set @s='ff00ff00ff00ff00ff00'
exec('select cast(0x'+@s+' as nchar)')
declare @sx nchar(6)
set @s='ffffffffffff';
set @i=0
set @s1=''
set @s2=N''
declare @t nvarchar(1000)while @i <6
begin
set @t = 'set @s1 = NCHAR(0x'+substring(@s,@i+1,2)+')'
exec sp_executesql @t,N'@s1 nvarchar(10) out',@s1 out
set @s2 = @s2 +@s1
set @i=@i+2
end
select @s2
/*
----------------------------------------------------------------------------------------------------
ÿÿÿ
*/
这些地方有些问题:
when substring(@h,@i,1) <='9' then substring(@h,@i,1)
when substring(@h,@i,1) ='A' then '10'
when substring(@h,@i,1) ='B' then '11'
when substring(@h,@i,1) ='C' then '12'
when substring(@h,@i,1) ='D' then '13'
when substring(@h,@i,1) ='E' then '14'
when substring(@h,@i,1) ='F' then '15' 其余的没仔细看,楼主再调试吧