有个字段叫KMDM,字段值不定长
现在我想批量修改该字段的值,首先判断该字段的长度是否大于等于9,若条件成立,则在第八个字符处加0
有朋友给我写了下述几个语句,但是不能执行:
1
update tablename
set KMDM=left(KMDM,7)+'0'+stuff(KMDM,1,7,'')
where len(KMDM)>=92
update tablename
set KMDM=left(KMDM,7)+'0'+right(KMDM,len(KMDM)-7)
where len(KMDM)>=9
3
Update TableName
Set KMDM = Stuff(KMDM, 8, 0, '0')
Where Len(KMDM) >= 94
update case when len(KMDM)>=9 then stuff(KMDM, 8, 0, '0') else KMDM end as KMDM
from tbName5
UPDATE [tablename]
SET KMDM=left(KMDM,7)+'0'+right(KMDM,len(KMDM)-7)
WHERE len(KMDM)>=9请帮忙看看!
现在我想批量修改该字段的值,首先判断该字段的长度是否大于等于9,若条件成立,则在第八个字符处加0
有朋友给我写了下述几个语句,但是不能执行:
1
update tablename
set KMDM=left(KMDM,7)+'0'+stuff(KMDM,1,7,'')
where len(KMDM)>=92
update tablename
set KMDM=left(KMDM,7)+'0'+right(KMDM,len(KMDM)-7)
where len(KMDM)>=9
3
Update TableName
Set KMDM = Stuff(KMDM, 8, 0, '0')
Where Len(KMDM) >= 94
update case when len(KMDM)>=9 then stuff(KMDM, 8, 0, '0') else KMDM end as KMDM
from tbName5
UPDATE [tablename]
SET KMDM=left(KMDM,7)+'0'+right(KMDM,len(KMDM)-7)
WHERE len(KMDM)>=9请帮忙看看!
Update TableName
Set KMDM = Stuff(cast(KMDM as varchar(20)), 8, 0, '0')
Where Len(cast(KMDM as varchar(20))) >= 9
UPDATE A
SET KMDM=
CASE WHEN LEN(KMDM) >9 THEN STUFF(KMDM,9,0,'0') ELSE KMDM END
通过,
如果是整数可选择出数据整理成整型
declare @i int
UPDATE A
SET
KMDM=CAST(CASE WHEN LEN(CAST(KMDM AS VARCHAR)) >9 THEN STUFF(CAST(KMDM AS VARCHAR),9,0,'0') ELSE CAST(KMDM AS VARCHAR) END AS INT)
set KMDM=left(rtrim(KMDM),7)+'0'+right(rtrim(KMDM),len(rtrim(KMDM))-7)
where len(KMDM)>=9
set KMDM=left(rtrim(KMDM),7)+'0'+right(rtrim(KMDM),len(rtrim(KMDM))-7)
where len(KMDM)>=9 and len(KMDM)<16