update on table

[raf]

Hi,
I've a table A with column:

COD VARCHAR2(20)

The col COD has this value:
1234
24788
013456
0356654 (there are so more of 1000 records)

It's possible update (in automatic) this col with these new values?
12000034
24000788
01003456
03056654

is necessary add zero until go to 8 charachters after first 2 char


Thanks
Raf

[Sameer]

I guess for automatic update you need to write a trigger for this.. But hope this helps.. You can write code for trigger using this.. You can process the :NEW.column value with the SELECT statement as shown..

SQL> CREATE TABLE test (id VARCHAR2(10));

Table created.

SQL> INSERT INTO test VALUES ('1234');

1 row created.

SQL> INSERT INTO test VALUES ('24788');

1 row created.

SQL> INSERT INTO test VALUES ('013456');

1 row created.

SQL> INSERT INTO test VALUES ('0356654');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT CASE
  2            WHEN LENGTH (a.id) < 8 THEN (SELECT    SUBSTR (id, 1, 2)
  3                                              || LPAD ('0', 8 - LENGTH (id), '0')
  4                                              || SUBSTR (id, 3)
  5                                         FROM test
  6                                        WHERE id = a.id)
  7            ELSE id
  8         END Col
  9  FROM test a
 10  ;

COL
--------------------------------------------------------------------------------
12000034
24000788
01003456
03056654

SQL>

HTH

Sameer

[jmodic]

UPDATE my_table
SET cod = SUBSTR(cod,1,2) || LPAD(SUBSTR(cod,3),6,'0');