Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error when using Case statement in SQL View

Posted on 2011-02-10
5
Medium Priority
?
264 Views
Last Modified: 2012-06-27
I'm trying to use a CASE statement in my SQL View.

I keep getting the following error; can't figure out what is wrong with my code.

Error message: Incorrect Syntax near ''.
SELECT     

CASE WHEN Right(Email, 5) = ‘a.com’

THEN

LEFT(Email, LEN(Email) - 21) + UPPER(SUBSTRING(Email, LEN(Email) - 20, 1)) + SUBSTRING(Email, LEN(Email) - 19, 6) + UPPER(SUBSTRING(Email, 
                      LEN(Email) - 13, 1)) + SUBSTRING(Email, LEN(Email) - 12, 6) + UPPER(SUBSTRING(Email, LEN(Email) - 6, 1)) + RIGHT(Email, 6) 

ELSE 

LEFT(Email, LEN(Email) - 11) + UPPER(SUBSTRING(Email, LEN(Email) - 10, 1))  + SUBSTRING(Email, LEN(Email) - 9, 2) 
 + UPPER(SUBSTRING(Email, LEN(Email) - 7, 1))  + Right(Email,7)

AS Email

FROM         dbo.AD_List

Open in new window

0
Comment
Question by:imstac73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 34866750
SELECT     

CASE WHEN Right(Email, 5) = 'a.com'

THEN

LEFT(Email, LEN(Email) - 21) + UPPER(SUBSTRING(Email, LEN(Email) - 20, 1)) + SUBSTRING(Email, LEN(Email) - 19, 6) + UPPER(SUBSTRING(Email, 
                      LEN(Email) - 13, 1)) + SUBSTRING(Email, LEN(Email) - 12, 6) + UPPER(SUBSTRING(Email, LEN(Email) - 6, 1)) + RIGHT(Email, 6) 

ELSE 

LEFT(Email, LEN(Email) - 11) + UPPER(SUBSTRING(Email, LEN(Email) - 10, 1))  + SUBSTRING(Email, LEN(Email) - 9, 2) 
 + UPPER(SUBSTRING(Email, LEN(Email) - 7, 1))  + Right(Email,7)

end AS Email

FROM         dbo.AD_List

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34867810
I think you mean:
Incorrect syntax near '‘'.
0
 
LVL 13

Expert Comment

by:Rahul Agarwal
ID: 34868145
Try this:


SELECT    

CASE Email WHEN Right(Email, 5) = ‘a.com’

THEN

LEFT(Email, LEN(Email) - 21) + UPPER(SUBSTRING(Email, LEN(Email) - 20, 1)) + SUBSTRING(Email, LEN(Email) - 19, 6) + UPPER(SUBSTRING(Email,
                      LEN(Email) - 13, 1)) + SUBSTRING(Email, LEN(Email) - 12, 6) + UPPER(SUBSTRING(Email, LEN(Email) - 6, 1)) + RIGHT(Email, 6)

ELSE

LEFT(Email, LEN(Email) - 11) + UPPER(SUBSTRING(Email, LEN(Email) - 10, 1))  + SUBSTRING(Email, LEN(Email) - 9, 2)
 + UPPER(SUBSTRING(Email, LEN(Email) - 7, 1))  + Right(Email,7)

AS Email

FROM         dbo.AD_List
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34868167
agarwalrahul,

Just out of curiousity, did you actually test that?
0
 

Author Comment

by:imstac73
ID: 34890706
agarwalrahul,, didn't work.  Incorrect syntax near '='.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 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