Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #444663] Tue, 23 February 2010 06:45 Go to next message
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 #444664 is a reply to message #444663] Tue, 23 February 2010 06:57 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
You can use CASE / INSTR
Re: sql query [message #444665 is a reply to message #444663] Tue, 23 February 2010 07:00 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: sequence into individual records
Next Topic: Pivot table using unix time stamp
Goto Forum:
  


Current Time: Tue Sep 27 22:57:09 CDT 2016

Total time taken to generate the page: 0.06642 seconds