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 |
|
robbie2883
Starting Member
2 Posts |
 Posted - 2014-08-16 : 00:29:05
|
| looking to extract email addressed from an email body over a few thousand records. not sure ltrim/rtrim will work well for me since every email address is different and there will be a few versions of the email body which the addresses are being pulled from. example 1This message was created automatically by the mail system (ecelerity).A message that you sent could not be delivered to one or more of itsrecipients. This is a permanent error. The following address(es) failed:>>> test1@test1.com (after RCPT TO): 550 5.1.1 <test1@test1.com>... User unknownexample 2This is an automatically generated Delivery Status Notification.Delivery to the following recipients failed. test2034@differenthost.comThe end result i'm looking for would be test@test.comtest2034@differenthost.com |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-08-16 : 02:55:08
|
| 1)Look for string with @ and then using the string functions in the link to extract the sub strings. The key is to find the start and finish position of each substringhttp://msdn.microsoft.com/en-GB/library/ms181984.aspx2)Another approach is to create a temp table or array of every substring using the spaces as the delimiters , and then run a select statement over the temp table to extract rows with @.Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Muj9
Yak Posting Veteran
75 Posts |
Posted - 2014-08-19 : 11:49:08
|
| you can use charindex to find the position of your @ in the string. and the use substring to only pull the email address. example of charindex and substring: -Charindex('String you looking for', yourcolumn)substring(yourcolumn,start postion,end postion') onces you know your staring position you can combine th 2 like so :-Substring(yourcolumn,Charindex('String you looking for', yourcolumn),Charindex('String you looking for', yourcolumn))as emailif you need to use like in order to find the position the you can use patindex instead.hope this helps.you |
|
|
|
|
|
|
Extract part of a string