Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Email Address Field 1

Status
Not open for further replies.

rothm1

Technical User
Mar 13, 2007
3
US
All of our Exempts have an email address entered in EV3, some may be company and some may be personal. Is there a way of puliing a report that shows only the last part of the email address?

I am trying to create a report for personal email addresses, but the email address is not a fixed number of characters.

Any help would be appreciated.

Melanie
 
Lets say you want the part starting with the "@" sign. You could use a SUBSTR in Oracel to pull just a bit of the field like this: SUBSTR(EMAIL_ADDRESS,5,10) This would bring back the part starting at pos 5 and returning up to 10 characters.

The problem with this is the starting point will very and the length will very.

Lets address the starting point. If I use the INSTR function I can find where the "@" is in the address. INSTR(EMAIL_ADDRESS,'@') will find the first occurance of a "@".

So if we combine the 2 we get this
SUBSTR(EMAIL_ADDRESS,INSTR(EMAIL_ADDRESS,'@'),10)

Now how do we deal with the length? We could calculate the length or just plug it with a big number and go with that. To calulate we could do
(LENGTH(EMAIL_ADDRESS) - INSTR(EMAIL_ADDRESS,'@'))
This is messy looking. What if we just pluged in something large like 99

SUBSTR(EMAIL_ADDRESS,INSTR(EMAIL_ADDRESS,'@'),99)


Specializing in ReportSmith Training and Consulting
 
I am using SQL Server, What would work?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top