MySQL SELECT CASE

The case statement is often used in stored procedures but can also be used on the command line and in regular queries. It is like a switch that tells in what situations to do a particular query. Say we have a list of fictional characters associated with specific books and we wish to abbreviate the books. This is probably the hard way to do it, but it would work:

SELECT name AS Name,
CASE book
WHEN "Tarzan" THEN "TZN"
WHEN "War and Peace" THEN "WAP"
END AS "book_abr"
FROM fict_chars

Given:

Id Name Desc Book Author
1 Jane Porter 5ft 4in blue eyes Tarzan Burroughs
2 Scarlett O'Hara 5ft 2in green eyes Gone with the Wind Mitchell
3 Natasha Rostov 5ft 6in brown eyes War and Peace Tolstoy

Our result will be:

Name book_abr
Jane Porter TZN
Scarlett O'Hara NULL
Natasha Rostov WAP

If the result is not provided for then it will be NULL. Note that the case is the column we are looking to draw our comparisons from. So when book is "Tarzan" then TZN becomes the datum in our book_abr column.

< SELECT Statement | Return to Index >


Send notes in disappearing ink!

Interesting Pages