declare @t table (name varchar(20)) insert into @t select 1001 union all select 1002001 union all select 1003001003003001 union all select 1008001 union all select 1005001002001select name=right(name,4)+ right(substring(name,5,3),2)+ right(substring(name,8,3),2)+ right(substring(name,11,3),2)+ right(substring(name,14,3),2) from @t /* name ------------------------ 1001 200101 300101030301 800101 2001010201 */
select name=right(name,4)+replace(right(name,len(name)-4),'00','0') from tb
create table #t(name varchar(20)) insert into #t select 1001 union all select 1002001 union all select 1003001003003001 union all select 1008001 union all select 1005001002001select name=left(name,4)+ right(substring(name,5,3),2)+ right(substring(name,8,3),2)+ right(substring(name,11,3),2)+ right(substring(name,14,3),2) from #t
declare @t table (name varchar(20)) insert into @t select 1001 union all select 1002001 union all select 1003001003003001 union all select 1008001 union all select 1005001002001select name=left(name,4)+ --应该是left 我写错了,修正一下 right(substring(name,5,3),2)+ right(substring(name,8,3),2)+ right(substring(name,11,3),2)+ right(substring(name,14,3),2) from @t /* name ------------------------ 1001 100201 100301030301 100801 1005010201 */
declare @t table (name varchar(20))
insert into @t
select 1001 union all
select 1002001 union all
select 1003001003003001 union all
select 1008001 union all
select 1005001002001select name=right(name,4)+
right(substring(name,5,3),2)+
right(substring(name,8,3),2)+
right(substring(name,11,3),2)+
right(substring(name,14,3),2) from @t
/*
name
------------------------
1001
200101
300101030301
800101
2001010201
*/
insert into #t
select 1001 union all
select 1002001 union all
select 1003001003003001 union all
select 1008001 union all
select 1005001002001select name=left(name,4)+
right(substring(name,5,3),2)+
right(substring(name,8,3),2)+
right(substring(name,11,3),2)+
right(substring(name,14,3),2)
from #t
declare @t table (name varchar(20))
insert into @t
select 1001 union all
select 1002001 union all
select 1003001003003001 union all
select 1008001 union all
select 1005001002001select name=left(name,4)+ --应该是left 我写错了,修正一下
right(substring(name,5,3),2)+
right(substring(name,8,3),2)+
right(substring(name,11,3),2)+
right(substring(name,14,3),2) from @t
/*
name
------------------------
1001
100201
100301030301
100801
1005010201
*/