Re: How to modifty the first chr of a field

Simona wrote:
> Hi! I've an insert like this
>
> insert into (....)
> select field1,field2,field from..
>
> my problem is that I would like to modify the contents of a particular field
>
> field2 is in this format: X006, X005, X008
>
> during the insert I want to modify X in 2 for obtain 2006, 2005, 2008 and
> save these values in my table
>
> Is it possible??
>
> Thanks

SQL> create table mysource(
  2 field1 varchar2(20),
  3 field2 varchar2(8),
  4 field number);

Table created.

SQL>
SQL> create table mydest(
  2 dfield1 varchar2(20),
  3 dfield2 varchar2(8),
  4 dfield number);

Table created.

SQL>
SQL> insert into mysource
  2 (field1, field2, field)
  3 values
  4 ('Test1','X001',123);

1 row created.

SQL>
SQL> insert into mysource
  2 (field1, field2, field)
  3 values
  4 ('Test2','X002',123);

1 row created.

SQL>
SQL> insert into mysource
  2 (field1, field2, field)
  3 values
  4 ('Test3','X003',123);

1 row created.

SQL>
SQL> insert into mysource
  2 (field1, field2, field)
  3 values
  4 ('Test4','X004',123);

1 row created.

SQL>
SQL> insert into mysource
  2 (field1, field2, field)
  3 values
  4 ('Test5','X005',123);

1 row created.

SQL>
SQL> insert into mysource
  2 (field1, field2, field)
  3 values
  4 ('Test6','X006',123);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select field1, field2, field
  2 from mysource;

FIELD1 FIELD2 FIELD

-------------------- -------- ----------
Test1                X001            123
Test2                X002            123
Test3                X003            123
Test4                X004            123
Test5                X005            123
Test6                X006            123

6 rows selected.

SQL>
SQL> insert into mydest
  2 (dfield1, dfield2, dfield)
  3 select
  4 field1, replace(field2, 'X', '2'), field   5 from mysource;

6 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select dfield1, dfield2, dfield
  2 from mydest;

DFIELD1 DFIELD2 DFIELD

-------------------- -------- ----------
Test1                2001            123
Test2                2002            123
Test3                2003            123
Test4                2004            123
Test5                2005            123
Test6                2006            123

6 rows selected.

SQL> David Fitzjarrell Received on Thu Jun 29 2006 - 16:07:58 CDT