Case when CHARINDEX

Got two emails:
john.smith@yahoo.com
jsmith@yahoo.com

Need to execute the following logic:
upper(substring(HQ_Ops.infoteam.Project_Locations."Project Manager",1,charindex('.',HQ_Ops.infoteam.Project_Locations."Project Manager")-1)) + ' ' + upper(substring(HQ_Ops.infoteam.Project_Locations."Project Manager", charindex('.',HQ_Ops.infoteam.Project_Locations."Project Manager")+1,charindex('@',HQ_Ops.infoteam.Project_Locations."Project Manager")-charindex('.',HQ_Ops.infoteam.Project_Locations."Project Manager")-1))

The problem is when the email address, 'jsmith@yahoo.com', doesn't contain '.' then the statement fails.
In this case I need to bring just the part before "@", in this example JSMITH.

Thus, I need to write CASE WHEN statement that would capture missing '.' and bring back just 'jsmith' otherwise executes the statement above 'JOHN SMITH'
Any help would be welcome!

InfoTeam

Asked: 2006-11-21

Advertise Here

Microsoft SQL Server

1 Solution

LVL 16

HillwaaaCommented: 2006-11-21

Hi InfoTeam,

case
when charindex('.',HQ_Ops.infoteam.Project_Locations."Project Manager") = 0 then
substring(HQ_Ops.infoteam.Project_Locations."Project Manager",1,charindex('@',HQ_Ops.infoteam.Project_Locations."Project Manager") -1)
else
-- your statement here
end

Cheers,
Hillwaaa

InfoTeamAuthor Commented: 2006-11-22

The problem with the statement is that "." exists in yahoo.com and that's why it wouldn't equal 0. We should just try the first part jsmith@

InfoTeamAuthor Commented: 2006-11-22

Seems like this one works:

CASE WHEN charindex('.', substring(HQ_Ops.infoteam.Project_Locations.[Project Manager], 1, charindex('@',
HQ_Ops.infoteam.Project_Locations.[Project Manager]) - 1)) = 0 THEN upper(substring(HQ_Ops.infoteam.Project_Locations.[Project Manager], 1,
charindex('@', HQ_Ops.infoteam.Project_Locations.[Project Manager]) - 1))
ELSE upper(substring(HQ_Ops.infoteam.Project_Locations.[Project Manager], 1, charindex('.', HQ_Ops.infoteam.Project_Locations.[Project Manager])
- 1)) + ' ' + upper(substring(HQ_Ops.infoteam.Project_Locations.[Project Manager], charindex('.', HQ_Ops.infoteam.Project_Locations.[Project Manager])
+ 1, charindex('@', HQ_Ops.infoteam.Project_Locations.[Project Manager]) - charindex('.', HQ_Ops.infoteam.Project_Locations.[Project Manager]) - 1))
END

LVL 1

Computer101Commented: 2007-01-11

PAQed with points refunded (250)

Computer101
EE Admin

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Expand your skills with this month's free Premium course.

Case when CHARINDEX

Join & Write a Comment Already a member? Login.

Featured Post