1, start

I saw this question post on CSDN yesterday:< Writing a stored procedure from 10 to 33 in SQL The question of the original post is as follows:

2. analysis

Conversion between different binary data, remember long ago wrote similar, participate in< Conversion between X and 10 The basic algorithm is the same.

Here, the requirement is that I, O and U should be excluded from the 26 letters in the conversion. In the original conversion method between X and 10, Char() function can not be used directly. The numbers and letters used need to be reordered and the conversion relocated.

2.1. Initialize a table variable @tb_tmp as follows:

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)
    insert into @tb_tmp(radix)
    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') 

2.2 Assuming a 10-ary variable @input_int=int=50, and dividing @input_int by 33 modules (@input_int%33) in a loop, we get the @tb_tmp matching id in 2.1 and locate the corresponding radix. Write the resulting Radix into the variable @output (@output = found Radix +@output). Each cycle, @input_int=@input_int/33, implements 10-digit splitting. When @input_int=0, exit the loop and return @output as a result value of 10 to 33.

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. Complete code:

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)
    insert into @tb_tmp(radix)
    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. test:

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

(end)