字符串字段如何+1递增呢?
我有一个编号字段,类似2007z0001,我希望前面对个字符不变,后面四个字符+1递增,这要怎么做呢?
[解决办法]
--create table test1(id int not null ,name char(10))
--insert into test1 values(3, '2007z0003 ')
--insert into test1 values(10, '2007z0009 ')
select top 1 dd =
case len(max(right(name,4))+1)
when 1 then left(name,5)+ '000 ' + cast((max(right(name,4))+1) as char)
when 2 then left(name,5)+ '00 '+cast((max(right(name,4))+1) as char)
when 3 then left(name,5)+ '0 '+cast((max(right(name,4))+1) as char)
end
from test1 group by name order by (right(name,4))+1 desc
-------------------------
| dd |
--------------------------
|2007z0010|
