* < 99999 - increment by 1
* = 99999 - A0000
* > A0000 - increment by 1 till A9999, then B0000
::::
-------------------------------------------------------------------------------------------------------- opt #1
CREATE OR REPLACE FUNCTION nxtvid
(curvid varchar2) RETURN varchar2 IS
vid varchar2(5);
len integer;
BEGIN
vid:='-----';
len:=length(curvid) ;
if len > 5
then
return vid;
end if;
select decode(trim(TRANSLATE(curvid, '0123456789',' ')), null,
decode(curvid,'99999','A0000', lpad(curvid+1,5,'0')),
decode(substr(curvid,2,4), '9999', chr(ascii(substr(curvid,1,1))+1)||'0000', substr(curvid,1,1)||lpad(substr(curvid,2,4)+1,4,'0'))
) into vid from dual;
RETURN vid;
exception
WHEN OTHERS then ::::::
END;
/
--------------------------------------------------------------------------- Opt #2
with function ni (curvid varchar2) return varchar2 as
vid varchar2(8);
begin
select decode(trim(TRANSLATE(curvid, '0123456789',' ')), null,
decode(curvid,'99999','A0000', lpad(curvid+1,5,'0')),
decode(substr(curvid,2,4), '9999', chr(upper(ascii(substr(curvid,1,1)))+1)||'0000', substr(curvid,1,1)||lpad(substr(curvid,2,4)+1,4,'0')))
into vid from dual;
return vid;
end;
select ni('E9999') from dual
--------------------------------------------------------------------------------- opt #3
UPDATE /*+ WITH_PLSQL */ PS_TABLE A
set FIELD_VID =(
with function ni (curvid varchar2) return varchar2 as
vid varchar2(8);
begin
select decode(trim(TRANSLATE(curvid, '0123456789',' ')), null,
decode(curvid,'99999','A0000', lpad(curvid+1,5,'0')),
decode(substr(curvid,2,4), '9999', chr(upper(ascii(substr(curvid,1,1)))+1)||'0000', substr(curvid,1,1)||lpad(substr(curvid,2,4)+1,4,'0')))
into vid from dual;
return vid;
end;
select ni('X0022') from dual
)
