说明：
1返回01，2返回02..99返回99
100返回A0，101返回A1...109返回A9
110返回B0，111返回B1...119返回B9
120返回C0，121返回C1...129返回C9
130
140
....以此类推
谢谢！
------解决方案--------------------
写个case when关系式吧，总共大概22种可能性。
------解决方案--------------------
/*假设T1为测试数据表*/
with T1 (num) as
(
select 1 union all
select 99 union all
select 105 union all
select 170 union all
select 180 union all
select 190
)
select num
, string = case
when num between 1 and 99 then replicate('0', 2-len(num)) + convert(varchar,num)
when num between 100 and 199 then case when char(left(num,2)-10+65) < 'I' then char(left(num,2)-10+65) + right(num,1)
else char(ascii(char(left(num,2)-10+65))+1) + right(num,1) end
end
from T1
------解决方案--------------------
if object_id('tempdb..#t1') is not null
drop table #t1
create table #t1(id int identity(10,1),val char(1))
declare @i int
set @i=ascii('A')
while @i<=ascii('Z')
begin
insert into #t1(val)
select char(@i)
set @i=@i+1
end
declare @val int
set @val=259
select isnull(val,'')+cast(@val%10 as varchar)
from #t1 where id=floor(@val/10.)
------解决方案--------------------
-- 建函数
create function dbo.fnxy
(@x int) returns varchar(10)
as
begin
declare @y varchar(10),@list varchar(10)
select @list='ABCDEFGHJK'
select @y=case when @x between 1 and 99
then right('00'+rtrim(@x),2)
when @x between 100 and 199
then substring(@list,(@x-100)/10+1,1)+right(rtrim(@x),1)
else '' end
return @y
end
-- 测试
select number 'x',
dbo.fnxy(number) 'y'
from master.dbo.spt_values
where type='P' and number between 1 and 199
-- 结果
/*
x y
----------- ----------
1 01
2 02
3 03
4 04
5 05
6 06
7 07
8 08
9 09
10 10
11 11
12 12
13 13
14 14
15 15
16 16
17 17
18 18
19 19
20 20
21 21
22 22
23 23
24 24
25 25
26 26
27 27
28 28
29 29
30 30