how to increment number in left pad if number already exists [message #393681] |
Tue, 24 March 2009 01:38  |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
i am updating every emp code by taking substring of first 2 letters in empcode and appending with 01.
my requirement is if 01 is already there then it should increment with next number.
UPDATE EMPLOYEES tl
set T1.EMP_CODE=
SUBSTR(TL.EMP_CODE, 0, 2) || '01';
empcode result
****** ******
1IMPOA 1I01
1IPOKK 1I02
2KPOIAA 2K01
2KPOOO 2K02
2Kg2O 2K03
3QOPP 3Q01
|
|
|
|
Re: how to increment number in left pad if number already exists [message #393699 is a reply to message #393681] |
Tue, 24 March 2009 01:57   |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
CREATE TABLE EMPLOYEES
(
EMP_CODE VARCHAR2(15)
);
INSERT INTO EMPLOYEES VALUES('1IMPOA');-- EMP_CODE should be updated with 1IM01
INSERT INTO EMPLOYEES VALUES('1IPOKK');--EMP_CODE should be updated with 1IP01
INSERT INTO EMPLOYEES VALUES('2KPOIAA');--EMP_CODE should be updated with 2KP01
INSERT INTO EMPLOYEES VALUES('2KPOOO');--EMP_CODE should be updated with 2KP02
INSERT INTO EMPLOYEES VALUES('2Kg2O');--EMP_CODE should be updated with 2Kg01
INSERT INTO EMPLOYEES VALUES('3QOPP');--EMP_CODE should be updated with 3Q01
INSERT INTO EMPLOYEES VALUES('3QSPP');--EMP_CODE should be updated with 3Q02
INSERT INTO EMPLOYEES VALUES('3QSPP');--EMP_CODE should be updated with 3Q03
INSERT INTO EMPLOYEES VALUES('3QSPP');--EMP_CODE should be updated with 3Q04
How to change this update statement.
UPDATE EMPLOYEES tl
set T1.EMP_CODE= SUBSTR(TL.EMP_CODE, 0, 2) || '01' --this number should be increment;
|
|
|
|