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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting only first part of E-Mail 1

Status
Not open for further replies.

vlfox

Technical User
Oct 13, 2005
40
US
by searching, I found this one: thread131-1439275 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 !
 
Try this:

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

Specializing in ReportSmith Training and Consulting
 
Hi, Charles;

When I tried your formula I got:

"Invalid length parameter passed to the substring function."

ThanX !
 
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?


Specializing in ReportSmith Training and Consulting
 
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 !
 
Yes'sir - we're non-hosted SQL...

ThanX !
 
Sorry for the confusion. It is SQLServer.

ThanX !
 
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

Specializing in ReportSmith Training and Consulting
 
Certainly not a surprise - You did it !! [thumbsup2]

Have a Star as well as my appreciation !

ThanX again...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top