SQL> select substr('asdfdfafda',-5,5) from dual;SUBSTR('ASDFDFAFDA',-5,5) ------------------------- fafda
通改改表中字段内容,保存为原内容的后5位,不够5位的就不作处理 用SUBSTR +case 解决SELECT CASE WHEN LENGTH('fabcde') >5 THEN SUBSTR('fabcde',-5,5) ELSE 'fabcde' END FROM dual --abcdeSELECT CASE WHEN LENGTH('bcde') >5 THEN SUBSTR('bcde',-5,5) ELSE 'bcde' END FROM dual --bcde
知道用用substr()函数,但不知道整句SQL怎么写 Update CCIC_LOSTcaR set CPH= trim(substr(CPH,10,-5)怎么不对
substr('12345',-1)执行后结果为5,意思为取倒数第一位,你的substr(CPH,10,-5)不对。 select case when length(CPH)>5 then substr(CPH,-5) else CPH end from dual
Update CCIC_LOSTcaR a set CPH=(select case when length(CPH)>5 then substr(CPH,-5) else CPH end from CCIC_LOSTcaR b where a.CPH=b.CPH)
-------------------------
fafda
用SUBSTR +case 解决SELECT CASE WHEN LENGTH('fabcde') >5 THEN SUBSTR('fabcde',-5,5)
ELSE 'fabcde' END FROM dual
--abcdeSELECT CASE WHEN LENGTH('bcde') >5 THEN SUBSTR('bcde',-5,5)
ELSE 'bcde' END FROM dual
--bcde
Update CCIC_LOSTcaR set CPH= trim(substr(CPH,10,-5)怎么不对
select case when length(CPH)>5 then substr(CPH,-5)
else CPH end from dual
else CPH end from CCIC_LOSTcaR b where a.CPH=b.CPH)
ORA-01427: 单行子查询返回多于一个行