CONCAT together with IF ELSE?

Question

CREATE TABLE `users` (
  `id`             int(11) AUTO_INCREMENT,
  `academicdegree` varchar(255),
  `name`           varchar(255),
  `firstname`      varchar(255),
  `sex`            enum('m','f')
)

SELECT TRIM(CONCAT_WS(" ", firstname, name)) AS fullname FROM users

Is there a way to add a useful salutation (Mr. or Mrs.) according to the ENUM value stored in the field "sex" in just one query?

Bonus track: I only can replace this part of the query: TRIM(CONCAT_WS(" ", firstname, name)) and ; is not allowed.

Answer 1

This will work just fine:

SELECT TRIM(CONCAT_WS(" ", IF(sex='m', 'Mr.', 'Mrs.'), firstname, name)) AS fullname FROM users

You could use a CASE statement, however if you've only got two options an IF() statement makes more sense.

Answer 2

SELECT TRIM(CONCAT_WS(" ", CASE sex WHEN 'm' THEN 'Mr.' WHEN 'f' THEN 'Mrs.' ELSE '' END, firstname, name)) AS fullname FROM users

(Note: this differs from the other answers in that it won't assume that a null sex implies 'Mrs.'. Though personally I'm not sure that even sex = 'f' should really imply 'Mrs.', since that salutation is normally not used for unmarried women, at least below a certain age.)