补充说明：ABCDEFGHJK, 只取到199，其中 i 不要，因为看着像1,就是说190返回K0而不是返回I01返回01，2返回02..99返回99100返回A0，101返回A1...109返回A9
110返回B0，111返回B1...119返回B9
120返回C0，121返回C1...129返回C9
130
140
....以此类推谢谢！
110返回B0，111返回B1...119返回B9
120返回C0，121返回C1...129返回C9
130
140
....以此类推谢谢！
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
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
31 31
32 32
33 33
34 34
35 35
36 36
37 37
38 38
39 39
40 40
41 41
42 42
43 43
44 44
45 45
46 46
47 47
48 48
49 49
50 50
51 51
52 52
53 53
54 54
55 55
56 56
57 57
58 58
59 59
60 60
61 61
62 62
63 63
64 64
65 65
66 66
67 67
68 68
69 69
70 70
71 71
72 72
73 73
74 74
75 75
76 76
77 77
78 78
79 79
80 80
81 81
82 82
83 83
84 84
85 85
86 86
87 87
88 88
89 89
90 90
91 91
92 92
93 93
94 94
95 95
96 96
97 97
98 98
99 99
100 A0
101 A1
102 A2
103 A3
104 A4
105 A5
106 A6
107 A7
108 A8
109 A9
110 B0
111 B1
112 B2
113 B3
114 B4
115 B5
116 B6
117 B7
118 B8
119 B9
120 C0
121 C1
122 C2
123 C3
124 C4
125 C5
126 C6
127 C7
128 C8
129 C9
130 D0
131 D1
132 D2
133 D3
134 D4
135 D5
136 D6
137 D7
138 D8
139 D9
140 E0
141 E1
142 E2
143 E3
144 E4
145 E5
146 E6
147 E7
148 E8
149 E9
150 F0
151 F1
152 F2
153 F3
154 F4
155 F5
156 F6
157 F7
158 F8
159 F9
160 G0
161 G1
162 G2
163 G3
164 G4
165 G5
166 G6
167 G7
168 G8
169 G9
170 H0
171 H1
172 H2
173 H3
174 H4
175 H5
176 H6
177 H7
178 H8
179 H9
180 J0
181 J1
182 J2
183 J3
184 J4
185 J5
186 J6
187 J7
188 J8
189 J9
190 K0
191 K1
192 K2
193 K3
194 K4
195 K5
196 K6
197 K7
198 K8
199 K9(199 行受影响)
*/