我oracle数据库里有几条记录PDM_MR_LeafNeedPlanDetailId
51d3ffb3-03e1-4750-b05c-b3303fe4fed5-TEMP
03bc2b2e-23ad-4dd9-ba6f-a3a4e2d46531-TEMP
2634058d-32c1-401b-bb48-09992d59589e-TEMP 现在我想把后面的-TEMP 去掉,怎么写?
update PDM_MR_LeafNeedPlanDetail set PDM_MR_LeafNeedPlanDetailId=substr(PDM_MR_LeafNeedPlanDetailId,0,length(PDM_MR_LeafNeedPlanDetailId)-5) where substr(PDM_MR_LeafNeedPlanDetailId,length(PDM_MR_LeafNeedPlanDetailId)-4,length(PDM_MR_LeafNeedPlanDetailId))= '-TEMP'这样不对!
51d3ffb3-03e1-4750-b05c-b3303fe4fed5-TEMP
03bc2b2e-23ad-4dd9-ba6f-a3a4e2d46531-TEMP
2634058d-32c1-401b-bb48-09992d59589e-TEMP 现在我想把后面的-TEMP 去掉,怎么写?
update PDM_MR_LeafNeedPlanDetail set PDM_MR_LeafNeedPlanDetailId=substr(PDM_MR_LeafNeedPlanDetailId,0,length(PDM_MR_LeafNeedPlanDetailId)-5) where substr(PDM_MR_LeafNeedPlanDetailId,length(PDM_MR_LeafNeedPlanDetailId)-4,length(PDM_MR_LeafNeedPlanDetailId))= '-TEMP'这样不对!
update PDM_MR_LeafNeedPlanDetail
set PDM_MR_LeafNeedPlanDetailId = substr(PDM_MR_LeafNeedPlanDetailId, 1, length(PDM_MR_LeafNeedPlanDetailId) - 5)
where substr(PDM_MR_LeafNeedPlanDetailId, -5) = '-TEMP';
from (select substr(PDM_MR_LeafNeedPlanDetailId, 1, length(PDM_MR_LeafNeedPlanDetailId) - 5) as PDM_MR_LeafNeedPlanDetailId
from PDM_MR_LeafNeedPlanDetail) t
where substr(PDM_MR_LeafNeedPlanDetailId, -5) = '-TEMP'
group by PDM_MR_LeafNeedPlanDetailId
having count(*) > 1;
UPDATE PDM_MR_LEAFNEEDPLANDETAIL
SET PDM_MR_LEAFNEEDPLANDETAILID=substr(PDM_MR_LEAFNEEDPLANDETAILID,0,length(PDM_MR_LEAFNEEDPLANDETAILID)-5)
WHERE substr(PDM_MR_LEAFNEEDPLANDETAILID,length(PDM_MR_LEAFNEEDPLANDETAILID)-4,5)='-TEMP';substr(string string, int a, int b)
参数1:string 要处理的字符串
参数2:a 截取字符串的开始位置(起始位置是0)
参数3:b 截取的字符串的长度(而不是字符串的结束位置)
所以where条件中最后一个参数是5而不是length(PDM_MR_LeafNeedPlanDetailId))