?
Solved

Extract Email Address from String

Posted on 2010-11-17
20
Medium Priority
?
1,521 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 101

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 101

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 101

Accepted Solution

by:
mlmcc earned 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

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
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how the fundamental information of how to create a table.
Simple Linear Regression

770 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