Sometimes you may need to cast data as boolean in MySQL. Here’s how to cast as boolean in MySQL. We will look at how to cast string as boolean and cast int as boolean.
How to Cast as Boolean in MySQL
Here are the steps to cast as boolean in MySQL. MySQL allows you to cast data using CAST and CONVERT functions. However, neither of them support conversion to boolean data type, out of the box. You need to cast as UNSIGNED INT, instead. Let us look at an example to cast string as boolean
Bonus Read : MySQL CAST vs CONVERT
How to Cast String as Boolean
Let’s say you have the following string column product that contains string values
mysql> select product from product_orders; +---------+ | product | +---------+ | A | | B | | C | | A | | B | | C | | A | | B | | C | +---------+
Let us say you want to convert string into boolean where product = True if it is A else False, then here’s the SQL query to cast string as boolean.
mysql> select cast(product='A' as unsigned) from product_orders; +-------------------------------+ | cast(product='A' as unsigned) | +-------------------------------+ | 1 | | 0 | | 0 | | 1 | | 0 | | 0 | | 1 | | 0 | | 0 | +-------------------------------+
In the above query, please note,
1. We CAST into UNSIGNED data type as neither CAST nor CONVERT functions support direct conversion to boolean data type
2. MySQL saves boolean data as tinyint(1) that is, 1 or 0, and not True/False values. Since tinyint can be easily saved as unsigned int we convert string into unsigned int.
3. We use a conditional expression (product=’A’) inside cast whose output is boolean. You cannot directly convert a string column into boolean using CAST or CONVERT. Here’s the output you will get if you try to do so.
mysql> select cast(product as unsigned) from product_orders; +---------------------------+ | cast(product as unsigned) | +---------------------------+ | 0 | | 0 | | 0 | | 0 | | 0 | | 0 | | 0 | | 0 | | 0 | +---------------------------+
You can modify the conditional expression as you need. For example, here’s the SQL query if you want to convert product =A or product =B as true, and rest as false.
mysql> select cast(product='A' or product='B' as unsigned) from product_orders; +----------------------------------------------+ | cast(product='A' or product='B' as unsigned) | +----------------------------------------------+ | 1 | | 1 | | 0 | | 1 | | 1 | | 0 | | 1 | | 1 | | 0 | +----------------------------------------------+
or you can also use the following query to get the same result.
mysql> select cast(product in ('A','B') as unsigned) from product_orders;
How to Cast Int as Boolean
Let us say you have the following int column amount in your table.
mysql> select amount from product_orders; +--------+ | amount | +--------+ | 250 | | 150 | | 200 | | 250 | | 210 | | 125 | | 350 | | 225 | | 150 | +--------+
Let’s say you want to convert int into boolean where amount<200 is True, else False. Here’s the SQL query to cast int as boolean.
mysql> select cast(amount<200 as unsigned) from product_orders; +------------------------------+ | cast(amount<200 as unsigned) | +------------------------------+ | 0 | | 1 | | 0 | | 0 | | 0 | | 1 | | 0 | | 0 | | 1 | +------------------------------+
You can also use MySQL Convert in place of MySQL CAST for each of the above queries. Here’s an example of above query, using MySQL CONVERT.
mysql> select convert(amount<200, unsigned) from product_orders;
Hopefully now you can easily cast as boolean in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.