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

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

robbie2883
Starting Member

USA
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:

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

example 2

This is an automatically generated Delivery Status Notification.

Delivery to the following recipients failed.

test2034@differenthost.com


The end result i'm looking for would be

test@test.com
test2034@differenthost.com




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

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 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

http://msdn.microsoft.com/en-GB/library/ms181984.aspx

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
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Muj9
Starting Member

United Kingdom
49 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.

you

Edited by - Muj9 on 08/19/2014 11:51:12
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000