Bulk update statement for numeric pattern

Question

I have a table TXN_METHOD with a column TXN_MTHD_CODE that currently has values from TXN_MTHD_1 to TXN_MTHD_35.

How can I perform a single bulk update statement that changes the numeric pattern to

TXN_MTHD_001
TXN_MTHD_002
......
TXN_MTHD_035

Answer 1

UPDATE TXN_METHOD SET TXN_MTHD_CODE = 'TXN_MTHD_' || LPAD(TO_CHAR(TO_NUMBER(REGEXP_REPLACE(TXN_MTHD_CODE, '[^0-9]', ''))), 3, '0') WHERE TXN_MTHD_CODE LIKE 'TXN_MTHD_%';

Above is my solution, the suffix changed to start from 001 to 035.

Answer 2

You can simplify your method to:

UPDATE TXN_METHOD
SET    TXN_MTHD_CODE = 'TXN_MTHD_'
         || LPAD(SUBSTR(TXN_MTHD_CODE, LENGTH('TXN_MTHD_') + 1), 3, '0')
WHERE  TXN_MTHD_CODE LIKE 'TXN_MTHD_%';

Which, for the sample data:

CREATE TABLE txn_method (txn_mthd_code) AS
  SELECT 'TXN_MTHD_' || LEVEL FROM DUAL CONNECT BY LEVEL <= 35;

Then, after the UPDATE, the table contains:

TXN_MTHD_CODE
TXN_MTHD_001
TXN_MTHD_002
TXN_MTHD_003
...
TXN_MTHD_033
TXN_MTHD_034
TXN_MTHD_035

fiddle