CREATE TABLE [tt] (
[no] [nvarchar] (20))
insert into tt select 'ms00005'
union all
select 'ms00004'
union all
select 'ms00003'
union all
select 'ms00002'
union all
select 'ms00001'SELECT 'ms'+ replicate('0',4-len(cast(RIGHT(CAST(MAX(no) AS NVARCHAR(10)),LEN(CAST(MAX(no) AS NVARCHAR(10)))-2)+1 as nvarchar(20))))+cast(RIGHT(CAST(MAX(no) AS NVARCHAR(10)),LEN(CAST(MAX(no) AS NVARCHAR(10)))-2)+1 as nvarchar(20)) from ttdrop table tt测试结果如下
所影响的行数为 5 行)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ms0006(所影响的行数为 1 行)
[no] [nvarchar] (20))
insert into tt select 'ms00005'
union all
select 'ms00004'
union all
select 'ms00003'
union all
select 'ms00002'
union all
select 'ms00001'SELECT 'ms'+ replicate('0',4-len(cast(RIGHT(CAST(MAX(no) AS NVARCHAR(10)),LEN(CAST(MAX(no) AS NVARCHAR(10)))-2)+1 as nvarchar(20))))+cast(RIGHT(CAST(MAX(no) AS NVARCHAR(10)),LEN(CAST(MAX(no) AS NVARCHAR(10)))-2)+1 as nvarchar(20)) from ttdrop table tt测试结果如下
所影响的行数为 5 行)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ms0006(所影响的行数为 1 行)
from tt
from tt
set @s='ms00009'
set @v=cast(cast(right(@s,1) as int)+1 as varchar)
select left(@s,2)+replace(space(5-len(@v)),' ','0')+@v結果﹕ms00010