# 10进制转33进制

### 1，开始

昨天在CSDN上看到这一个提问帖子：《用SQL写一个存储过程10进制转33进制代码 》，原帖的问题是这样：

就是我想在SQL中创建一个存储过程“T10TO33”可以实现将10进制的数据转换成33进制。
33进制规则是：1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,J,K,L,M,N,P,Q,R,S,T,V,W,X,Y,Z,0

### 2.分析

2.1.初始化一个表变量@tb_tmp,内容如下：

declare @tb_tmp as table(id int identity(0,1) primary key,radix char(1))
;with cte as (select top (36) row_number() over(order by getdate())-1 as id from sys.columns)
select case when id>9 then char(55+id) else rtrim(id) end as radix from cte where char(55+id) not in ('I','O','U') 

set @output=''
while(1=1)
begin
select @output=Convert(nvarchar(1024),Case (@input_int%33) when 0 then rtrim(@input_int%33) else (select radix from @tb_tmp where id=@input_int%33) end+@output),@input_int=@input_int/33
if @input_int=0 break
end

### 3.完整代码：

if object_id('T10TO33') Is not null
Drop Proc T10TO33
Go
create procedure T10TO33(
@input_int int,
@output nvarchar(1024) output
)
as
set nocount on
declare @tb_tmp as table(id int identity(0,1) primary key,radix char(1))
;with cte as (select top (36) row_number() over(order by getdate())-1 as id from sys.columns)
select case when id>9 then char(55+id) else rtrim(id) end as radix from cte where char(55+id) not in ('I','O','U')
set @output=''
while(1=1)
begin
select @output=Convert(nvarchar(1024),Case (@input_int%33) when 0 then rtrim(@input_int%33) else (select radix from @tb_tmp where id=@input_int%33) end+@output),@input_int=@input_int/33
if @input_int=0 break
end
go

### 4.测试：

declare @reuslt nvarchar(1024)

exec T10TO33 0,@reuslt output
print @reuslt --0

exec T10TO33 7,@reuslt output
print @reuslt --7

exec T10TO33 32,@reuslt output
print @reuslt --Z

exec T10TO33 33,@reuslt output
print @reuslt --10

exec T10TO33 34,@reuslt output
print @reuslt --11

exec T10TO33 50,@reuslt output
print @reuslt --1H

exec T10TO33 99,@reuslt output
print @reuslt --30

exec T10TO33 100,@reuslt output
print @reuslt --31

（完）

posted @ 2017-02-09 14:05 ok_008 阅读(...) 评论(...) 编辑 收藏