Solved

Extract Email Address from String

Posted on 2010-11-17
20
1,451 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

729 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