sql语句来增加字符串位数

有表employee里面有一字段empno,现在empno字段的值有3位,4位的.例如400,4100.现在要让它们变成000400,004100.在不足6位数的前面补0.请各位大侠指点.(用SQL,要用到游标)

 

 

用不用游标都可以，不用更简洁。
主要是用到一些字符串函数，如len--求字符串的长度，ltrim－－去除字符串的前导空格 rtrim---去除字符串的尾部空格

方法一：直接用update语句

update employee set empno='000'+ltrim(rtrim(empno)) where len(ltrim(rtrim(empno)))=3
update employee set empno='00'+ltrim(rtrim(empno)) where len(ltrim(rtrim(empno)))=4

方法二：采用游标
declare cur scroll cursor for select empno from employee for update
open cur
declare @ex char(10)
fetch first from cur into @ex
while @@fetch_status=0
   begin
     if len(ltrim(rtrim(@ex)))=3
        update employee set empno='000'+ltrim(rtrim(empno)) where current of cur
     else
        if len(ltrim(rtrim(@ex)))=4 
           update employee set empno='00'+ltrim(rtrim(empno)) where current of cur
     fetch next from cur into @ex
   end
close cur
deallocate cur 

 

  0