create table a(col varchar(10))
insert a select 'ca000001'
union all select 'ca000002'
union all select 'ca000003'
union all select 'ca000004'
union all select 'ca000005'
union all select 'ca000006'
union all select 'ca000007'
union all select 'ca000008'
union all select 'ca000009'
union all select 'ca000010'
select * from a
select 'ca'+right('000000'+cast(cast(right(max(col),6) as int)+1 as varchar(10)),6) from a
declare @t table(id char(8))
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t
insert into @t select 'ca'+right(1000001+isnull(right(max(id),6),0),6) from @t