Email Address Field 1

[rothm1]

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

 

[CharlesCook]

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)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[rothm1]

I am using SQL Server, What would work?

 

[CharlesCook]

SUBSTRING(EMAIL_ADDRESS,CHARINDEX('@',EMAIL_ADDRESS),99)

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[rothm1]

Thank you so much!