CODEWITHSUNDEEP
mysqlsqlselectnull
cce85899
cce85899

Reputation: 29

Case for NULL in SQL

I have this code but its not working .

When NULL is there it should give '' out and no title but it does not.

SELECT employee.pin,
CASE gender
WHEN 'M' or (employee.titel IS NULL) THEN CONCAT('Mister ', ' ', ' ', person.fname, ', ' )
WHEN 'W' or (employee.titel IS NULL) THEN CONCAT('Mrs ', ' ', ' ',     person.fname, ', ' )
WHEN 'M' THEN CONCAT('Mister ', employee.titel, ' ', person.fname, ', ' )
WHEN 'W' THEN CONCAT('Mrs ', employee.titel, ' ', person.fname, ', ' )
END AS salutation
FROM person,employee

where person.pin=employee.pin
ORDER BY pin

Upvotes: 0

Views: 70

Answers (2)

fthiella
fthiella

Reputation: 49089

You can use CONCAT_WS that will just skip null values:

SELECT
  employee.pin,
  CASE WHEN gender='M' THEN CONCAT_WS(' ', 'Mister', employee.titel, person.fname)
       WHEN gender='W' THEN CONCAT_WS(' ', 'Mrs', employee.titel, person.fname)
  END AS salutation
FROM
  person INNER JOIN employee
  ON person.pin=employee.pin
ORDER BY pin

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726839

You would have to use the other CASE syntax, because NULL is not equal to anything:

CASE
WHEN gender='M' or (employee.titel IS NULL) THEN CONCAT('Mister ', ' ', ' ', person.fname, ', ' )
WHEN gender='W' or (employee.titel IS NULL) THEN CONCAT('Mrs ', ' ', ' ',     person.fname, ', ' )
WHEN gender='M' THEN CONCAT('Mister ', employee.titel, ' ', person.fname, ', ' )
WHEN gender='W' THEN CONCAT('Mrs ', employee.titel, ' ', person.fname, ', ' )
WHEN gender IS NULL THEN ...
END AS salutation

Upvotes: 1

Related Questions