SQL16进制2进制，10进制(转)

From:http://h11h99.blog.51cto.com/230273/50098

16进制2进制，10进制

 

/*===============
  --将16进制数据初步转化为2进制

  --created by h11h99

create function HToT(@str char(1))
returns varchar(4)
as
begin
declare @var varchar(8000)
set @var=case
when @str='0' then '0000'
when @str='1' then '0001'
when @str='2' then '0010'
when @str='3' then '0011'
when @str='4' then '0100'
when @str='5' then '0101'
when @str='6' then '0110'
when @str='7' then '0111'
when @str='8' then '1000'
when @str='9' then '1001'
when @str='A' then '1010'
when @str='B' then '1011'
when @str='C' then '1100'
when @str='D' then '1101'
when @str='E' then '1110'
when @str='F' then '1111'
end
return @var
end
*/

/* 将长字符串转化为每四位一行 */

declare  @string varchar(8000)
declare  @i int
declare  @table table(id int,content varchar(10))
set @string='0EEA680031003100680039003900'
set @i=1
while (@i<=len(@string)/4)
begin
set @string=replace(@string,char(10),'')
set @string=replace(@string,char(13),'')
insert into @table(id,content) values(@i,substring(@string,(@i-1)*4+1,4))
set @i=@i+1
end
update @table set content=right(content,2)+left(content,2)
select *  from @table

/*
结果
1 EA0E
2 0068
3 0031
4 0031
5 0068
6 0039
7 0039
*/

/* 将16进制转化为2进制 */
declare @str varchar(20)
declare @res varchar(20)
declare @i int
set @i=1
set @str='0039'
set @res=''
while @i<5
begin
set @res=@res+dbo.HToT(substring(@str,@i,1))
set @i=@i+1
end
select @res '十六进制对应的二进制为'

/*
结果
EA0E:1110101000001110
0068:0000000001101000
0031:0000000000110001
0031:0000000000110001
0039:0000000000111001
0039:0000000000111001
*/

/* 将2进制转化为10进制 */
declare @str varchar(20)
declare @i int
declare @p int
declare @sum int
set @sum=0
set @str='0000000000111001'
while charindex('1',@str)<>0
begin
set @i=charindex('1',@str)
set @str=stuff(@str,charindex('1',@str),1,'s')
set @p=len(@str)-@i
set @sum=@sum+power(2,@p)
end
select @sum as '二进制对应的十进制为'
select nchar(@sum) '对应的字符为'

/*
结果
EA0E:1110101000001110  59918
0068:0000000001101000  104    h
0031:0000000000110001  49     1
0031:0000000000110001  49     1
0068:0000000001101000  104    h
0039:0000000000111001  57     9
0039:0000000000111001  57     9
*/

declare @b varchar(8000)
declare @index int
declare @times int
declare @char varchar(10)
declare @length int
declare @result varchar(100)
set @b='fffffdddzzzzaaccca'
set @index=2
set @times=1
set @char=''
set @result=''
--set @char=''
SET @length=len(@b)
while @index<@length
  begin
 set @char=substring(@b,@index,1)
  if (substring(@b,@index-1,1)=substring(@b,@index,1))
  begin
  set @times=@times+1
  end
 IF @times>=3
 begin
/*
  if charindex(@char,@result)=0
  begin
  set @result=@result+@char
  end
*/
 set @result=@result+@char
 set @times=0
 end
 set @index=@index+1
  
  end
  PRINT @result

 

create table list (uid varchar(8),regdate datetime default getdate())
create table t(uid varchar(8))
insert into list(uid) values('111345')
insert into list(uid) values('123456')
insert into list(uid) values('122249')
insert into list(uid) values('123444')
create procedure gethree(@a varchar(1))
as
declare  @b int, @str varchar(8)
declare cur cursor local for select uid from plat_uidlist
begin
open cur
fetch next from cur into @str
while @@fetch_status =0
begin
select @b=charindex(replace(@a+@a+@a,' ',''),sid) from list where uid = @str
if @b>0
begin
insert into t values(@str)
end
fetch next from cur into @str
end

declare @a int
set @a=0
while @a<10
begin
exec getthree @a
set @a=@a+1
end