Solved

Extract Email Address from String

Posted on 2010-11-17
20
1,285 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
  • 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
 

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 40

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 40

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 40

Expert Comment

by:Sharath
ID: 34160531
In your original question, the content mentioned is of single record or 5 records?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

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

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 34

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 34

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now