need to display part of string in varchar field

[andreadd]

SQL 2008 R2 / GoldMine Premium 9

i posted this question in the crystal reports forum as well - If this can be done in SQL - wahoo for me and I will gladly take it. but to do html interpretation of the email, I am running the SQL query thru crystal and formatting it as i goes.

with that said....

I need to write this query & report that will ultimately export general email correspondence. The way GoldMine stores these emails is as image so SQL sees it as binary. I have my view currently casting it as varchar:

CAST(CAST(rfc822 as varbinary(max), as varchar(max)) as [Da Email]

The first 4 lines of every email look like this:

Date: Tue, 23 Feb 2010 09:46:17 -0500 CHAR(10)
From: andreadd <andreadd@mayemail.com> CHAR(10)
Subject: Wahoo LP Meeting CHAR(10)
To: Joe Schmoe <schmoe@theiremail.com> CHAR(10)

i need to just display schmoe@theiremail.com

is this possible and how? I figured i am stuck in crystal since i have to cast the email into varchar first in order to be able to see the lines.

 

[markros]

Well, if the e-mail is always between < and > then it's not hard to extract it using charindex and SUBSTRING functions after first converting to varchar(max).

PluralSight Learning Library