CODEWITHSUNDEEP
mysqlsql
RealSlimShady
RealSlimShady

Reputation: 19

mySQL: How to display a string upon encountering a specific string in a column using CASE?

What I want to accomplish is that, when I encounter the string Rotterdam, I want to replace it with N/A I was able to do it using IF command, can someone tell me why my CASE command is giving error?

Code: If

SELECT id,if(name like 'rotterdam','N/A',name)
   ,
    COUNTRYCODE,
    DISTRICT,
    POPULATION
    
FROM city
LIMIT 10;

Case (incorrect)

SELECT id,/* if(name like 'rotterdam','N/A',name) */
   CASE
        WHEN name LIKE 'ROTTERDAM' THEN 'N/A'
        ELSE SELECT name
    END,
    COUNTRYCODE,
    DISTRICT,
    POPULATION
    
FROM city
LIMIT 10;

The table: [Table image]1

My approach: [using case command]2 (you can see the error here)

[using if command (works)]3

Desired Output: [you can see N/A in the second column]4

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Don't use a second select:

(CASE WHEN name LIKE 'ROTTERDAM' THEN 'N/A'
      ELSE name
 END)

You could use SELECT, but it would be silly and require a subquery:

(CASE WHEN name LIKE 'ROTTERDAM' THEN 'N/A'
      ELSE (SELECT name)
 END)

Note that the SELECT (without parentheses) would not work with IF() either.

Upvotes: 1

Related Questions