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!
InfoTeamAsked:
Who is Participating?
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0
 
HillwaaaCommented:
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
0
 
InfoTeamAuthor Commented:
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@
0
 
InfoTeamAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.