updata table set a=left(a,8)*10000+right(a,length(a)-8) 写错了
还是错误Msg 195, Level 15, State 10, Line 1 'length' is not a recognized built-in function name.
新的错误 Arithmetic overflow error converting expression to data type int. The statement has been terminated.
create table #t080715(test varchar(12)) insert into #t080715 select '000020071231' union all select '120071231' union all select '9920071231' select * from #t080715update #t080715 set test=(case len(test) when 12 then right(test,8)+left(test,4) when 11 then right(test,8)+'0'+left(test,3) when 10 then right(test,8)+'00'+left(test,2) when 9 then right(test,8)+'000'+left(test,1) else right(test,8)+'0000' end )
select right(date1,8)+right(10000+substring(date1,1,len(date1)-8),4) from tabadate1表列名
declare @tb table(test varchar(12)) insert into @tb select '000020071231' union all select '120071231' union all select '9920071231' select test, right(test,8)+ right(10000 + cast(left(test,case when len(test)>8 then len(test)-8 end) as int),4) as newVal from @tb /* test newVal ------------ -------------------- 000020071231 200712310000 120071231 200712310001 9920071231 200712310099 */
写错了
'length' is not a recognized built-in function name.
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
insert into #t080715
select '000020071231' union all
select '120071231' union all
select '9920071231'
select * from #t080715update #t080715
set test=(case len(test) when 12 then right(test,8)+left(test,4)
when 11 then right(test,8)+'0'+left(test,3)
when 10 then right(test,8)+'00'+left(test,2)
when 9 then right(test,8)+'000'+left(test,1)
else right(test,8)+'0000' end
)
from tabadate1表列名
insert into @tb
select '000020071231' union all
select '120071231' union all
select '9920071231'
select test,
right(test,8)+ right(10000 + cast(left(test,case when len(test)>8 then len(test)-8 end) as int),4) as newVal
from @tb
/*
test newVal
------------ --------------------
000020071231 200712310000
120071231 200712310001
9920071231 200712310099
*/