CHARINDEX

[sallyr]

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

 

[CharlesCook]

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

 

[sallyr]

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

 

[CharlesCook]

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

 

[sallyr]

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

 

[CharlesCook]

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

 

[sallyr]

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

Sally R