select no, case when len([name]) =4 then stuff([name],2,0,'0') when len([name]) =3 then stuff([name],2,0,'00') when len([name]) =2 then stuff([name],2,0,'000') else [name] end as newdname from t1
建表的时候加上check约束: create table testLength ( id int not null identity, name char(5) check (len(name)=5) )这样就不会出现小于5的字段了。再加个限制就必须以‘B'开头了 alter table testlength add aaaa char(5) check (len(aaaa)=5 and aaaa like 'B%')这样就不用更新了
case when len([name]) =4 then stuff([name],2,0,'0')
when len([name]) =3 then stuff([name],2,0,'00')
when len([name]) =2 then stuff([name],2,0,'000')
else [name] end as newdname from t1
create table testLength
(
id int not null identity,
name char(5) check (len(name)=5)
)这样就不会出现小于5的字段了。再加个限制就必须以‘B'开头了
alter table testlength
add aaaa char(5) check (len(aaaa)=5 and aaaa like 'B%')这样就不用更新了