Extract just email addresses out of a field

binaryman101
binaryman101 used Ask the Experts™
on
I have a query that runs and produces me exactly what I want.  It just has more information in the result than what I need.  It has text in the result that I do not want.  For example I get the following:

Customer email changed from 12345@test.com; bobby@brown.com  to 12345@testnew.com; bobby@yellow.com.

This is always the same but I would like to only display everything after "to"

12345@testnew.com; bobby@yellow.com
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0)
ORDER BY CallDateTime DESC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
SELECT LTRIM(RIGHT(urColumn, CHARINDEX(' to ', urColumn )- 4) )

Author

Commented:
My column name is Issue.

Author

Commented:
I get the following error:

Invalid Length parameter passed to the right function.
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, LTRIM(RIGHT('Issue', CHARINDEX(' to ', 'Issue') - 4)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0)
ORDER BY CallDateTime DESC

Open in new window

Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

AneeshDatabase Consultant
Top Expert 2009

Commented:
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, LTRIM(RIGHT(Issue, CHARINDEX(' to ', Issue) - 4)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0)
WHERE  CHARINDEX(' to ', Issue)  > 4
ORDER BY CallDateTime DESC

Author

Commented:
Error:
Incorrect syntax near the keyword 'WHERE'
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, LTRIM(RIGHT(Issue, CHARINDEX(' to ', Issue) - 4)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0)
WHERE     CHARINDEX(' to ', Issue) > 4
ORDER BY CallDateTime DESC

Open in new window

AneeshDatabase Consultant
Top Expert 2009

Commented:
oops

SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, LTRIM(RIGHT(Issue, CHARINDEX(' to ', Issue) - 4)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0)
and        CHARINDEX(' to ', Issue) > 4
ORDER BY CallDateTime DESC

Author

Commented:
Well this is now not producing an error, but it is not producing the right results either.  Thoughts?  This is what it is producing.

om;  to cguidry@bgfood.com; CGross@tdm.cc; jbrown@bgfood.com;sgoodlett@tdm.cc .

om; sgoodlett@tdm.com to cguidry@bgfood.com; CGross@tdm.cc; jbrown@bgfood.com;sgoodlett@tdm.cc .

om; sgoodlett@tdm.com to cguidry@bgfood.com; CGross@tdm.cc; jbrown@bgfood.com; sgoodlett@tdm.cc.
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, LTRIM(RIGHT(Issue, CHARINDEX(' to ', Issue) - 4)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0) AND (CHARINDEX(' to ', Issue) > 4)
ORDER BY CallDateTime DESC

Open in new window

Commented:
use the following syntax
Substring(Issue,(charindex('to',issue)+2,Len(issue))

Author

Commented:
Where would I place that in my existing syntax?

Commented:
in Place of LTRIM(RIGHT(Issue, CHARINDEX(' to ', Issue) - 4)) AS Expr1
use  Substring(Issue,(charindex('to',issue)+2,Len(issue))

SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, Substring(Issue,(charindex('to',issue)+2,Len(issue))
 AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0)
ORDER BY CallDateTime DESC
 

Author

Commented:
Incorrect syntax near ','.

Author

Commented:
Error Mesage: Incorrect syntax near ','.


SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, Substring(Issue, (charindex('to', issue) + 2, Len(issue)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0) AND (CHARINDEX(' to ', Issue) > 4)
ORDER BY CallDateTime DESC

Open in new window

Author

Commented:
Any other suggestions?
Commented:
Sorry CharIndex syntax was not closed properly

Substring(Issue,( (charindex('to', issue)) + 2), Len(issue))

Author

Commented:
Still not the results I had hoped for...

mer email changed from stephen.selman@hanson.biz;jasong@tdm.cc;sgoodlett@tdm.cc to barbarak@tdm.cc;Susan.Criswell@LehighHanson.com.
mer email changed from susan.criswell@lehighhanson.com;sgoodlett@tdm.cc to barbarak@tdm.cc;Susan.Criswell@LehighHanson.com.
mer email changed from sgoodlett@tdm.cc;Susan.Criswell@LehighHanson.com to barbarak@tdm.cc;Susan.Criswell@LehighHanson.com.
mer email changed from thomas.holamon@hanson.biz;jasong@tdm.cc;sgoodlett@tdm.cc to barbarak@tdm.cc;Susan.Criswell@LehighHanson.com.
mer email changed from tjwilson2004@embarqmail.com to tjwilson2004@embarqmail.com;m.kehoe@mchsi.com.
mer email changed from wanda.weibel@hanson.biz to barbarak@tdm.cc;Susan.Criswell@LehighHanson.com.
mer email changed from wanda.weibel@hanson.biz;jasong@tdm.cc;sgoodlett@tdm.cc to barbarak@tdm.cc;Susan.Criswell@LehighHanson.com.
mer email changed from bkunce@kconline.com;support@email.u4corp.com to bkunce@kconline.com.

I am using....
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, SUBSTRING(Issue, CHARINDEX('to', Issue) + 2, LEN(Issue)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0) AND (CHARINDEX(' to ', Issue) > 4)
ORDER BY CallDateTime DESC

Open in new window

Author

Commented:
Got it!

Thanks all for the help!
SELECT     TOP (100) PERCENT CallDateTime, AccountNumber, CallID, Issue, CallType, SUBSTRING(Issue, CHARINDEX(' to ', Issue) + 3, LEN(Issue)) AS Expr1
FROM         Telebill.dbo.CustomerServiceInitialCalls
WHERE     (CallType = N'Notification') AND (Resolved = 0) AND (CHARINDEX(' to ', Issue) > 4)
ORDER BY CallDateTime DESC

Open in new window

Author

Commented:
Several Items not accurate, but definately pointed me int he right direction.

Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial