基础题 ，求解答~

Question

 表中的数是1~100
我想把它变为001,002,003.......010....100
但是我update错了
update tb set shu=shu+'00' where len(shu)=1
现在就变成100,200,300.......10.......100
请问怎么样把1~9后面的00去掉

Answer 1

大于100的,用
update tb set shu='00'+left(shu,1) from tb where shu>'100'
去除.
100,没法去除,因为你没说出与原有100的区别.

Answer 2

select number,number1=right('000'+ltrim(number),3) from master..spt_values
where type='p' and number between 1 and 100
/*
number      number1
----------- -------
1           001
2           002
3           003
4           004
5           005
6           006
7           007
8           008
9           009
10          010
11          011
12          012
13          013
...
93          093
94          094
95          095
96          096
97          097
98          098
99          099
100         100
*/

Answer 3

update tb set shu=left(shu,1) where convert(int,shu)<10

Answer 4

update tb set shu='00'+left(shu,1) from tb where shu>'100'