Email Addresses

[AJCG]

All,

I have been having trouble doing soomething that sounds easy but is turning out to be quite difficult, i will explain:-

I have a table with a lot of emails in it like say -

blah@someone.com
b.23l@a207.navy.com.au
adam.chris-gran@ca.co.uk

and so on, I would now like to split these down into a table like -


Col1 Col2 Col3 Col4 Col5
blah someone com
b.231 a207 navy com au
ada.chris-gran ca co uk

Any Ideas I seem to end up with the name before the @ and a nasty mess with the end? like :-

b.231@a.navy..com
ada.chris-gran@ca..co

Things like that any ideas?

Ajcg

 

[Turkbear]

Since you did not supply any examples of what you tried,
you should use a combination of instr and substr functions to parse out the desired text..
See the docs on SqlPlus functions...

 

[DaPi]

REPLACE(<string>,'..','.') should help

 

[AJCG]

Hi Again...

I have tried a different tack but I am still getting problems namely with end of lines... say I do this :

v_part_third := INSTR (v_email_sec, '.', 1, 1);
v_part_fourth := INSTR (v_email_sec, '.', 1, 2);
v_part_fifth := INSTR (v_email_sec, '.', 1, 3);
v_part_sixth := INSTR (v_email_sec, '.', 1, 4);

v_email2 := SUBSTR (v_email_sec, 1, v_part_third - 1);
v_email3 := SUBSTR (
v_email_sec,
v_part_third
+ 1,
v_part_fourth
- v_part_third
- 1
);


Well when I get on to say a sixth part this has passed the end of many email addresses (Not Naval ones this is the last field for them) it outputs the entire address so I am getting :-


col1 col2 col3..... col6
a20 a304 navy au
some aol com some@aol.com

This is rather annoying...! Could someone explain this to me?

 

[Turkbear]

Hi, I did not try you code snippet, but if you test for 0
for the returned value for each Instr function you can then determine the exact amount of parts to decode a particular string..
If the string has fewer than the Instr functions (say only 2 periods ) the the 0 values for all the v_part_[n] after that, when used in your substr math, may cause problems....

 

[AJCG]

I have made a few alterations to my procedure and cut the name part of the email address and the domain information into seperate variables, I am then splitting down the domain info... This is still an issue I am having trouble with...