Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Extract part of a string
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 08/16/2014 :  00:29:05  Show Profile  Reply with Quote
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 1

This message was created automatically by the mail system (ecelerity).

A message that you sent could not be delivered to one or more of its
recipients. This is a permanent error. The following address(es) failed:

>>> (after RCPT TO): 550 5.1.1 <>... User unknown

example 2

This is an automatically generated Delivery Status Notification.

Delivery to the following recipients failed.

The end result i'm looking for would be

Edited by - robbie2883 on 08/16/2014 00:29:43

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 08/16/2014 :  02:55:08  Show Profile  Visit jackv's Homepage  Reply with Quote
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 substring

2)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
Go to Top of Page

Yak Posting Veteran

United Kingdom
75 Posts

Posted - 08/19/2014 :  11:49:08  Show Profile  Reply with Quote
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 email

if you need to use like in order to find the position the you can use patindex instead.

hope this helps.


Edited by - Muj9 on 08/19/2014 11:51:12
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000