Solved

Extract Email Address from String

Posted on 2010-11-17
20
1,394 Views
Last Modified: 2012-05-10
I have a memo field that I need to extract an email address out of.  The string lists the email address in two places in each record.  Here is an example of the data:

CREATED via EMAIL interface by user@yahoo.com.
Originating Message:%category=value here
%customer=value here
%description=various values here
%FROM_EMAIL=user@yahoo.com

The text in bold above is always the same, but all of the other text is different between records.  I want to extract just the email address and place it in my report.

Can anyone help me with this?
0
Comment
Question by:almaler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +2
20 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34156649
select RIGHT(@urcolumn, len(urColumn)-CHARINDEX('FROM_EMAIL=',urColumn)-10 ) from your table
0
 

Author Comment

by:almaler
ID: 34156678
The email addresses themselves can be very short or very long in length.  I don't see any viable way to use length or specific character positions due to this fact.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34156791
di you try that query ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:almaler
ID: 34156828
I tried it in SQL and I received the following message:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@description".
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 34157564
oops typo

select RIGHT(description, len(description)-CHARINDEX('FROM_EMAIL=',description)-10 ) from yourTable
0
 

Author Comment

by:almaler
ID: 34158078
Ok, I tried that and I am getting the following message:

Msg 8116, Level 16, State 1, Line 1
Argument data type ntext is invalid for argument 1 of len function.

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34158427
try converting ntext to nvarchar.
select RIGHT(CONVERT(nvarchar(100),urcolumn), len(CONVERT(nvarchar(100),urcolumn))-CHARINDEX('FROM_EMAIL=',CONVERT(nvarchar(100),urcolumn))-10 ) 
  from @table

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34158428
select RIGHT(CONVERT(nvarchar(100),urcolumn), len(CONVERT(nvarchar(100),urcolumn))-CHARINDEX('FROM_EMAIL=',CONVERT(nvarchar(100),urcolumn))-10 )
  from your_table
0
 

Author Comment

by:almaler
ID: 34158441
I tried the following:

select RIGHT(CONVERT(nvarchar(100),description), len(CONVERT(nvarchar(100),description))-CHARINDEX('FROM_EMAIL=',CONVERT(nvarchar(100),description))-10 ) from act_log

Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the RIGHT function.

 
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34160531
In your original question, the content mentioned is of single record or 5 records?
0
 

Author Comment

by:almaler
ID: 34164754
It's a single record.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34169117
Can you try this.
select substring(convert(nvarchar(3000),urcolumn),
                 charindex('FROM_EMAIL=',convert(nvarchar(3000),urcolumn))+11,
                 LEN(convert(nvarchar(3000),urcolumn)))
  from your_table

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34178552
Can you do this in the Crystal Report?

If so try this

Add a formula
Local StringVar Array myStrings;
myStrings := Split([yourField},"="0
myStrings[ubound(myStrings)]

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 34178916
A few corrections to mlmcc's formula.  The second line should read:

myStrings := Split({yourField},"=");


 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34181317
My shift key is sticking and I don't catch it all the time.  

Thanks
mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 34194370
No problem.
0
 

Author Comment

by:almaler
ID: 34196306
Sharath_123,

I ran your query and the query ran successfully, but the resulting output wasn't the desired email address.

mlmcc and James0628,
The formula appeared to work fine, but then when I ran the report I received an error that said "A subscript must be between 1 and the size of the array."


 
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 34197071
You must have some NULL fields.

Local StringVar Array myStrings;
If Not IsNull({YourField}) then
(
     myStrings := Split({yourField},"=")
    myStrings[ubound(myStrings)]
)
else
     ""

mlmcc
0
 

Author Comment

by:almaler
ID: 34197446
Still receiving the same error with that formula as well:

Local StringVar Array myStrings;
If Not IsNull({act_log.description}) then
(
    myStrings := Split({act_log.description},"=");
    myStrings[ubound(myStrings)]
)
else
     ""
0
 

Author Comment

by:almaler
ID: 34198158
mlmcc,
I found an anamoly in one of the database records.  After cleaning that up, your solution is working great!  Thank you for your help :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question