Solved

Case when CHARINDEX

Posted on 2006-11-21
5
907 Views
Last Modified: 2008-02-26
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!
0
Comment
Question by:InfoTeam
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17991172
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
 

Author Comment

by:InfoTeam
ID: 17995895
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
 

Author Comment

by:InfoTeam
ID: 17996064
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18298378
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now