sql query [message #444663] |
Tue, 23 February 2010 06:45  |
hemalatha_81
Messages: 16 Registered: February 2010
|
Junior Member |
|
|
Hello all .
I am working on Formatting of data where I need to search and locate for some characters from the Col1.If the search string has been matched , the whole string has to be converted to some other characters to the col2.
For example.
From the Col1 if the string has '.to.' it should be converted as 'denc' to the col2
Similarly if the string has '.from.' it should be converted as 'Edec' to the col2
If there is no '.to.' or '.from'. it should be as null in the col2
Expected result.
Col1 COL2
234354234545.to. fox.com denc
6255524235454.From.fox.com Edec
435345345895743.to.joac.com Denc
435347856347.from.com Edec
SQL> create table data(col1 varchar2(96),col2 varchar2(96));
Table created.
SQL> insert into data values ('234354234545.to. fox.com','');
1 row created.
SQL> insert into data values ('6255524235454.From.fox.com','');
1 row created.
SQL> insert into data values ('435345345895743.to.joac.com','');
1 row created.
SQL> insert into data values ('43653475890.from.fox.com','');
1 row created.
SQL> insert into data values ('45749750023948.com','');
Awaiting for someone valuable response.
|
|
|
|
Re: sql query [message #444665 is a reply to message #444663] |
Tue, 23 February 2010 07:00  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select col1,
2 case when instr(col1,'.to.') > 0 then 'denc'
3 when instr(col1,'.from.') > 0 then 'Edec'
4 else null
5 end col2
6 from data
7 /
COL1 COL2
------------------------------ ----
234354234545.to. fox.com denc
6255524235454.From.fox.com
435345345895743.to.joac.com denc
43653475890.from.fox.com Edec
45749750023948.com
Regards
Michel
|
|
|