×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!
  • Students Click Here

*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

CHARINDEX

CHARINDEX

CHARINDEX

(OP)
I am trying to successfully execute the following statement. It works except the else(LEFT..). I need to retrieve all the characters from the left of a field until it reaches an @. I do not want to include the @. Any suggestions? Thanks so much. Sally

CASE
PS_PERSONAL_DATA.EMAIL_ADDRESS WHEN NULL
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN ''
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN 'none'
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
ELSE
LEFT(PS_PERSONAL_DATA.EMAIL_ADDRESS,CHARINDEX('@',(PS_PERSONAL_DATA.EMAIL_ADDRESS) ) )
END


RE: CHARINDEX

Try this:

LEFT(PS_PERSONAL_DATA.EMAIL_ADDRESS,(INSTR(PS_PERSONAL_DATA.EMAIL_ADDRESS,'@')-1))

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

RE: CHARINDEX

(OP)
Charles,

Thanks for your reply.  I am getting an SQL execution error.  Line 71: Incorrect syntax error near')' when I use the code you suggested.  This error is never helpful.  Any other thoughts would be greatly appreciated.  Thanks.

Sallyr

CASE                                                                                       
PS_PERSONAL_DATA.EMAIL_ADDRESS WHEN NULL
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN ''
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN 'none'
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
ELSE
LEFT(PS_PERSONAL_DATA.EMAIL_ADDRESS,(INSTR(PS_PERSONAL_DATA.EMAIL_ADDRESS,'@')-1))
END
 
 

RE: CHARINDEX

in a new derived field what does this return?

INSTR(PS_PERSONAL_DATA.EMAIL_ADDRESS,'@')

If it is a number then what does this do?

LEFT(PS_PERSONAL_DATA.EMAIL_ADDRESS,INSTR(PS_PERSONAL_DATA.EMAIL_ADDRESS,'@'))


CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

RE: CHARINDEX

(OP)
Charles,

I have solved the original problem by using a combo of my and your code.  By using a -1 in the correct place, the field has all characters to the left of the @ sign.  
See below.  

Now I am trying to remove spaces from the field created if the last name is less than 5 characters.  

This is the last hurdle before I move the report into production.  Any suggestions would be appreciated.

Thanks,
Sally

CASE                                                                                       
PS_PERSONAL_DATA.EMAIL_ADDRESS WHEN NULL
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN ''
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN 'none'
THEN
LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
ELSE
LEFT(PS_PERSONAL_DATA.EMAIL_ADDRESS,CHARINDEX('@',(PS_PERSONAL_DATA.EMAIL_ADDRESS) ) -1)
END
 
 

RE: CHARINDEX

Try this:


CASE                                                                                       
PS_PERSONAL_DATA.EMAIL_ADDRESS WHEN NULL
THEN
TRIM(LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5)) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN ''
THEN
TRIM(LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5)) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
WHEN 'none'
THEN
TRIM(LOWER(SUBSTRING(PS_PERSONAL_DATA.LAST_NAME,1,5)) +
SUBSTRING(PS_PERSONAL_DATA.FIRST_NAME,1,1) +
SUBSTRING(PS_PERSONAL_DATA.MIDDLE_NAME,1,1))
ELSE
LEFT(PS_PERSONAL_DATA.EMAIL_ADDRESS,CHARINDEX('@',(PS_PERSONAL_DATA.EMAIL_ADDRESS) ) -1)
END

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

RE: CHARINDEX

(OP)
Thanks so much.  I have the results I wanted.  I appreciate the time you spent to help me.

Sally R

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