×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Getting only first part of E-Mail

Getting only first part of E-Mail

Getting only first part of E-Mail

(OP)
by searching, I found this one:  thread131-1439275: Email Address Field which got just the last part of an e-mail address.

I adjusted part of the formula and got to just the first part (the individual's name) by using:

SUBSTRING(PS_PERSONAL_DATA.EMAIL_ADDRESS,1,CHARINDEX('@',PS_PERSONAL_DATA.EMAIL_ADDRESS))

the result I got is now:  JSMITH@

It's close and most definitely workable, but I just wondered how I could clean it up further to remove the "@" to leave just:  JSMITH

ThanX !

RE: Getting only first part of E-Mail

Try this:

SUBSTRING(PS_PERSONAL_DATA.EMAIL_ADDRESS,1,(CHARINDEX('@',PS_PERSONAL_DATA.EMAIL_ADDRESS)-1))

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Getting only first part of E-Mail

(OP)
Hi, Charles;

When I tried your formula I got:

"Invalid length parameter passed to the substring function."

ThanX !

RE: Getting only first part of E-Mail

Create a new derived field with just this in it.

(CHARINDEX('@',PS_PERSONAL_DATA.EMAIL_ADDRESS)-1)

Does that work?

Does it ever return 0 or a negitive number?
 

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Getting only first part of E-Mail

(OP)
Using your test formula, I didn't get any zeros, but did get -1 for the folks that don't have an e-mail address listed.  The rest counted the characters in the alias correctly.

Does this make it easier or harder?  bigglasses

Really it's not a huge deal if you don't want to spend too much time on it - there's many more interesting issues than this one...

ThanX !

RE: Getting only first part of E-Mail

What brand Database are you using? It's not Oracle is it SQLServer?

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Getting only first part of E-Mail

(OP)
Yes'sir - we're non-hosted SQL...

ThanX !

RE: Getting only first part of E-Mail

I need to know the BRAND of SQL you are using. Oracle and SQLServer are examples.

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Getting only first part of E-Mail

(OP)
Sorry for the confusion.  It is SQLServer.

ThanX !

RE: Getting only first part of E-Mail

Try this:

CASE TRIM(PS_PERSONAL_DATA.EMAIL_ADDRESS)
     /* Get rid of empty email addresses */
     WHEN IS NULL THEN ' '
     ELSE
          CASE CHARINDEX('@',TRIM(PS_PERSONAL_DATA.EMAIL_ADDRESS))
               /* get rid of email address without an @ */
               WHEN 0 THEN ' '
               /* Return the address up to the @ */
               ELSE SUBSTRING(PS_PERSONAL_DATA.EMAIL_ADDRESS,1,(CHARINDEX('@',PS_PERSONAL_DATA.EMAIL_ADDRESS)-1))
          END
END  

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

RE: Getting only first part of E-Mail

(OP)
Certainly not a surprise - You did it !! thumbsup2

Have a Star as well as my appreciation !

ThanX again...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close