CODEWITHSUNDEEP
mysqlsql
powtac
powtac

Reputation: 41080

CONCAT together with IF ELSE?

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.

Upvotes: 4

Views: 4746

Answers (2)

ruakh
ruakh

Reputation: 183612

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.)

Upvotes: 3

Bojangles
Bojangles

Reputation: 101553

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.

Upvotes: 5

Related Questions