MySQL CASE Function Examples
To understand the working of MySQL CASE function, let’s look at some examples of CASE function.
First let’s create a table:
Demo MySQL Database
CREATE TABLE float01001
(
user_id int NOT NULL AUTO_INCREMENT,
float_val float,
PRIMARY KEY(user_id)
);
INSERT float01001(float_val)
VALUES (1.9);
INSERT float01001(float_val)
VALUES (1.1);
INSERT float01001(float_val)
VALUES (3.9);
INSERT float01001(float_val)
VALUES (5.0);
INSERT float01001(float_val)
VALUES (10.9);
Now, we will check some CASE function queries with output base on this table:
Example 1: Using the CASE() function and getting the output.
SELECT float_val,
CASE
WHEN float_val > 5 THEN "The value is greater than 5"
WHEN float_val = 5 THEN "The value is 5"
ELSE "The value is under 5"
END as float_txt
FROM float01001;
Output:
float_val | float_txt |
+-----------+-----------------------------+
| 1.9 | The value is under 5 |
| 1.1 | The value is under 5 |
| 3.9 | The value is under 5 |
| 5 | The value is 5 |
| 10.9 | The value is greater than 5
Example 2:
Using the CASE() function and checking if the length of the stated float value is greater than, or less than, or equal to 4.
SELECT float_val,
CASE
WHEN LENGTH(float_val) > 4 THEN "The length is greater than 4"
WHEN LENGTH(float_val) = 4 THEN "The length is 4"
ELSE "The length is less than 4"
END as float_txt
FROM float01001;
Output:
float_val | float_txt |
+-----------+---------------------------+
| 1.9 | The length is less than 4 |
| 1.1 | The length is less than 4 |
| 3.9 | The length is less than 4 |
| 5 | The length is less than 4 |
| 10.9 | The length is 4
MySQL CASE() Function
MySQL CASE function is a conditional statement that returns a value when the first condition is met.
Once a condition is met, the CASE function does not check for other conditions. If no condition is met it returns the output in ELSE part.
Contact Us