Home » SQL & PL/SQL » SQL & PL/SQL » Sql -Formatting Data
Sql -Formatting Data [message #444695] Tue, 23 February 2010 09:31 Go to next message
hemalatha_81
Messages: 16
Registered: February 2010
Junior Member
Hello all .

I am working on Formatting of data where the col1 column data is having string with an email id .i need to strip the email id and store the rest of the string on the Col2

For example.

Col1 COL2

Willian,ian <ian.willains@domain.com > Willian,ian
Jaon,arc <iarc.jaon@domina.com > Jaon,arc
Mathew.richard <Richard,Mathew@ssudomai.com> Mathew.richard
Issac.sicerc <sicerc.issac@ususomini.com > Issac.sicerc


SQL> create table data1(col1 varchar2(96),col2 varchar2(96));

Table created.

SQL>
SQL> insert into data1 values ('Willian,iroert <iro.willains@domain.com > ','');

1 row created.

SQL> insert into data1 values ('Jaon,arc <iarc.jaon@domina.com >','');

1 row created.

SQL> insert into data1 values ('Mathew.richard <Richard,Mathew@ssudomai.com>','');

1 row created.

SQL> insert into data1 values ('Issac.sicerc <sicerc.issac@ususomini.com >','');
Re: Sql -Formatting Data [message #444696 is a reply to message #444695] Tue, 23 February 2010 09:33 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use substr and instr
Re: Sql -Formatting Data [message #444698 is a reply to message #444695] Tue, 23 February 2010 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please do not post the execution of your test case, just the test case itself:
create table data1(col1 varchar2(96),col2 varchar2(96));
insert into data1 values ('Willian,iroert <iro.willains@domain.com > ','');
insert into data1 values ('Jaon,arc <iarc.jaon@domina.com >','');
insert into data1 values ('Mathew.richard <Richard,Mathew@ssudomai.com>','');
insert into data1 values ('Issac.sicerc <sicerc.issac@ususomini.com >','');
commit;

SQL> select col1, 
  2         substr(col1,instr(col1,'<')+1,instr(col1,'@')-instr(col1,'<')-1) col2
  3  from data1
  4  /
COL1                                          COL2
--------------------------------------------- ------------------
Willian,iroert <iro.willains@domain.com >     iro.willains
Jaon,arc <iarc.jaon@domina.com >              iarc.jaon
Mathew.richard <Richard,Mathew@ssudomai.com>  Richard,Mathew
Issac.sicerc <sicerc.issac@ususomini.com >    sicerc.issac

4 rows selected.

or
SQL> select col1,
  2         substr(col1,1,instr(col1,' ')-1) col2
  3  from data1
  4  /
COL1                                          COL2
--------------------------------------------- -------------------
Willian,iroert <iro.willains@domain.com >     Willian,iroert
Jaon,arc <iarc.jaon@domina.com >              Jaon,arc
Mathew.richard <Richard.Mathew@ssudomai.com>  Mathew.richard
Issac.sicerc <sicerc.issac@ususomini.com >    Issac.sicerc

depending on which part you want.

Regards
Michel

[Updated on: Tue, 23 February 2010 09:59]

Report message to a moderator

Re: Sql -Formatting Data [message #444700 is a reply to message #444698] Tue, 23 February 2010 10:02 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh I see you swap the names:
SQL> select col1,
  2         regexp_replace(col1,'^([^,.]+)([,.])([^ ]+) .*$','\3\2\1') col2
  3  from data1
  4  /
COL1                                          COL2
--------------------------------------------- -------------------
Willian,iroert <iro.willains@domain.com >     iroert,Willian
Jaon,arc <iarc.jaon@domina.com >              arc,Jaon
Mathew.richard <Richard.Mathew@ssudomai.com>  richard.Mathew
Issac.sicerc <sicerc.issac@ususomini.com >    sicerc.Issac

Of course it should be better if example and test case match.

And please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Tue, 23 February 2010 10:04]

Report message to a moderator

Previous Topic: Pivot table using unix time stamp
Next Topic: Query re-writing help
Goto Forum:
  


Current Time: Fri Dec 09 08:11:15 CST 2016

Total time taken to generate the page: 0.12551 seconds