Getting only first part of E-Mail 1

[vlfox]

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 !

 

[CharlesCook]

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

 

[vlfox]

Hi, Charles;

When I tried your formula I got:

"Invalid length parameter passed to the substring function."

ThanX !

 

[CharlesCook]

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

 

[vlfox]

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?

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 !

 

[CharlesCook]

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

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[vlfox]

Yes'sir - we're non-hosted SQL...

ThanX !

 

[CharlesCook]

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

 

[vlfox]

Sorry for the confusion. It is SQLServer.

ThanX !

 

[CharlesCook]

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

 

[vlfox]

Certainly not a surprise - You did it !!

Have a Star as well as my appreciation !

ThanX again...