Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-28 : 08:41:43
|
| Hassan writes "Hi I need to parse out the Email Address from a filed called EmailAddress. Here are two records from EmailAddress filed. 1 - X400:c=US;a=MCI;p=SCN;o=SMS;s=Alvarez;g=Arlene;ou1=ISE1;%NOTES:Arlene Alvarez@MSExchange%SMTP:arlen.alvarez@siemen.com2 - NOTES:Ali Bani-Hashemi@MSExchange%SMTP:ali-hashemi@siemen.com%X400:c=US;a=MCI;p=SCN;o=SMS;s=Bani-Hashemi;g=Ali;ou1=ISE1;As you see in one the email Address is at the end of string, in other the Email address is in the middle of string.I tried CharIndex, SubString, Right and Left function, but I could parse out the Email address. Little help would be appreciated.Hassan" |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-05-29 : 00:45:17
|
| Hi HassanIt's ugly but something like this will do it...select case when email like '%[%]%' then substring(email, 0, patindex('%[%]%',email)) else email endfrom (select substring(email, patindex('%SMTP:%@%', email) + len('SMTP:'), len(email) - (patindex('%SMTP:%@%', email) + len('SMTP:')-1)) as email from tA) as A-That's broken into a nested select (just to make it easier to understand) - but you could do it all in one line....My suggestion however would be to condsider writing a sp to extract the email address and store it into another field in your table. Then you have it when you need it.Hope that helps--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
|
|
|
Parse String