How to extract specfic information from a field

[ADW]

Hi All,

I need to extract specific information from a field in a table which looks like the following;

Jim (jim@someone.com)

Basically I need to extract the email part; jim@someone.com.

Obvioulsy the length of the field is going to be different and is dependant on the persons name, i.e. it could look the following;

James (james@someone.com)

Any suggestions?

Thanks in advance

 

[bborissov]

Is the eMail part always included in brackets?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ADW]

Yes the email part is always in brackets.

 

[bborissov]

On top of my head, not a very generic solution, because it assume you have eMail ALWAYS in brackets and the eMail is ALWAYS in the first place in the expression. So if you have something like:

'Jim (the greatest) bla-bla (Jim@someone.com)' this solution wouldn't work:

[COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](5000))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'Jim (jim@someone.com)'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'James (james@someone.com)'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'James asdasd asd asd (james@someone.com)'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'James 333333333333333333 (james@someone.com)'[/color])


[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]LEFT[/color]([COLOR=#FF00FF]SUBSTRING[/color](Fld1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'('[/color],Fld1)+1,5000),
                            [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]')'[/color],[COLOR=#FF00FF]SUBSTRING[/color](Fld1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'('[/color],Fld1)+1,5000))-1)
[COLOR=blue]FROM[/color] @Temp
[COLOR=blue]WHERE[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'('[/color],Fld1) > 0

I'll try to invent something more generic, but can't promise it would be right now.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Here one more generic solution. It is based on [@] char, but it again expects that the eMail is always enclosed with brackets:

[COLOR=blue]DECLARE[/color] @Temp [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]varchar[/color](5000))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'Jim (jim@someone.com)'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'James (james@someone.com)'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'James asdasd asd asd (james@someone.com) (ppppppp)'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Temp [COLOR=blue]VALUES[/color] ([COLOR=red]'James (asd) 333333333333333333 (james@someone.com)'[/color])


[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]REVERSE[/color]([COLOR=#FF00FF]LEFT[/color]([COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]REVERSE[/color](Fld1),[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'@'[/color],[COLOR=#FF00FF]REVERSE[/color](Fld1))+1,5000),
                    [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'('[/color],[COLOR=#FF00FF]SUBSTRING[/color]([COLOR=#FF00FF]REVERSE[/color](Fld1),[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'@'[/color],[COLOR=#FF00FF]REVERSE[/color](Fld1))+1,5000))-1))+
       [COLOR=#FF00FF]LEFT[/color]([COLOR=#FF00FF]SUBSTRING[/color](Fld1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'@'[/color],Fld1),5000),
       [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]')'[/color],[COLOR=#FF00FF]SUBSTRING[/color](Fld1,[COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'@'[/color],Fld1),5000))-1)
[COLOR=blue]FROM[/color] @Temp
[COLOR=blue]WHERE[/color] [COLOR=#FF00FF]CHARINDEX[/color]([COLOR=red]'@'[/color],Fld1) > 0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ADW]

Thanks mate - I will give this a try ASAP!!!

 

[ADW]

Works a treat thanks!