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!
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@
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
case
when charindex('.',HQ_Ops.infot
substring(HQ_Ops.infoteam.
else
-- your statement here
end
Cheers,
Hillwaaa