Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Extract Email Address from String

Posted on 2010-11-17
20
Medium Priority
?
1,629 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

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!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Loops Section Overview

579 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