select substr('12~12~12~34~56~1~',1,length('12~12~12~34~56~1~')-2) from dual;
DECLARE V_RESULT VARCHAR2(255) := '12~12~12~34~56~1~'; vc VARCHAR2(255); vpt varchar2(255); vlt varchar2(255) := ''; l number := length(V_RESULT); s number := 0; BEGIN
while s <= l loop vc := substr(v_result, s); SELECT REGEXP_SUBSTR(vc, '[1-9]{2}~?') INTO vpt FROM DUAL; s := s + length(vpt); --DBMS_OUTPUT.PUT_LINE(vpt); vlt := vlt || vpt; end loop; DBMS_OUTPUT.PUT_LINE(vlt); END;
这样更简单:DECLARE V_RESULT VARCHAR2(255); BEGIN SELECT REGEXP_REPLACE('12~12~12~34~56~1~', '~[0-9]{3,}~|~[0-9]{1}~', '~') INTO V_RESULT FROM DUAL; DBMS_OUTPUT.PUT_LINE(V_RESULT); END;
DECLARE str VARCHAR2(255) := '12~12~12~34~56~1444~'; temp VARCHAR2(255); s number := 0; BEGIN s:=instr(str,'~',-2);--倒数第二个~的位置 temp:=substr(str, 0,s); DBMS_OUTPUT.PUT_LINE(temp); END;输出:12~12~12~34~56~
DECLARE
V_RESULT VARCHAR2(255) := '12~12~12~34~56~1~';
vc VARCHAR2(255);
vpt varchar2(255);
vlt varchar2(255) := '';
l number := length(V_RESULT);
s number := 0;
BEGIN
while s <= l loop
vc := substr(v_result, s);
SELECT REGEXP_SUBSTR(vc, '[1-9]{2}~?') INTO vpt FROM DUAL;
s := s + length(vpt);
--DBMS_OUTPUT.PUT_LINE(vpt);
vlt := vlt || vpt;
end loop;
DBMS_OUTPUT.PUT_LINE(vlt);
END;
V_RESULT VARCHAR2(255);
BEGIN SELECT REGEXP_REPLACE('12~12~12~34~56~1~',
'~[0-9]{3,}~|~[0-9]{1}~',
'~')
INTO V_RESULT
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
END;
DECLARE
str VARCHAR2(255) := '12~12~12~34~56~1444~';
temp VARCHAR2(255);
s number := 0;
BEGIN
s:=instr(str,'~',-2);--倒数第二个~的位置
temp:=substr(str, 0,s);
DBMS_OUTPUT.PUT_LINE(temp);
END;输出:12~12~12~34~56~
s:=instr(ivrpack,'~',1,9);
temp:=substr(ivrpack,0,s);