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

.

Relevant Pages

  • Re: Ado Navigate to Last Record
    ... just covers SQL syntax, not ADO. ... the last SessionID could belong to another user. ... SELECT Field2 FROM MyTable WHERE field IN FROM ... Select Field1, Field2 ...
    (microsoft.public.data.ado)
  • Re: Surely This Is Simple
    ... You might try a query whose SQL looks something like this: ... is named "Field1", and the field you identified as is named ... "Field2". ...
    (microsoft.public.access.queries)
  • Re: Finding records with equal values in 3 fields
    ... it's way beyond my SQL ... >>>has other records that has same values in the fields Field1, Field3, ... >> FROM tblMyTable INNER JOIN qryGetCounts AS C ...
    (microsoft.public.access.queries)
  • Very Strange: field value is not updated if you try to set it to its default value
    ... I have filled a datatable with the existing rows from my sql table. ... When I change Field1 to any value other than '' the data is saved ... Profiler and sure enough, my values were getting replaced with the ... Since I was manually creating my update command I decided to try the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Very Strange: field value is not updated if you try to set it to its default value
    ... I have filled a datatable with the existing rows from my sql table. ... When I change Field1 to any value other than '' the data is saved ... Profiler and sure enough, my values were getting replaced with the ... Since I was manually creating my update command I decided to try the ...
    (microsoft.public.dotnet.framework.adonet)