In a specific case, I needed to cast some string (VARCHAR) field to INTEGER, for doing some comparison. I know there is a CAST function available for this type of conversion, so it should be simple.
I opened MySQL Workbench and typed the following line:
-- This is wrong way to cast as INTEGER
SELECT CAST(column_name AS INT) FROM table_name;It gave me an error. Ok, maybe I need to change the INT to INTEGER (there is no logic behind it, as those are the same thing in MySQL). Let’s try with INTEGER:
-- This is wrong way to cast as INTEGER
SELECT CAST(column_name AS INTEGER) FROM table_name;Didn’t work. Gave me the same error.
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘int)
Ok, MySQL is asking me to check the manual. Let’s check the manual then.
https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast
After checking the manual, I found the answer and here it is.
Correct way to CAST as an Integer
To CAST any data type to an Integer in MySQL, you need to CAST it as SIGNED (for getting a signed integer) or UNSIGNED (for getting unsigned integer). Like bellow.
To get a SIGNED value use:
SELECT CAST(column_name AS SIGNED) FROM table_name;
Casting as SIGNED will generate a SIGNED BIGINT value.
To get UNSIGNED value use:
SELECT CAST(column_name AS UNSIGNED) FROM table_name; Casting as UNSIGNED will generate a UNSIGNED BIGINT value.
Problem solved. Now let’s check how different data types behave when CAST as an integer is applied.
Cast VARCHAR/TEXT as Integer
If you cast a VARCHAR/TEXT or any string field to an integer, then you will get the digits from the start of the string until anything non-numeric character exists.
SELECT CAST(some_varchar_column_name AS SIGNED) FROM table_name;| Original String Value | Value after CAST as SIGNED INTEGER |
|---|---|
| 10 | 10 |
| -10 | -10 |
| 8.1.0 | 8 |
| 1024test | 1024 |
| 1024.987test654 | 1024 |
| Test1024 | 0 |
Cast DATE as Integer
If you CAST a DATE field to an Integer, you will get the timestamp value.
SELECT CAST(some_date_column_name AS SIGNED) FROM table_name;| Original Date Value | Value after CAST as SIGNED INTEGER |
|---|---|
| 2018-12-20 | 1544678315 |
| 2019-03-14 | 1545023915 |
| 2019-03-21 | 1545110435 |
| 2019-09-23 | 1569086040 |
Cast DATETIME or TIMESTAMP as Integer
If you convert a DATETIME or TIMESTAMP, then you get the same thing, just without the separator symbols, hyphen (-), or color (:)
SELECT CAST(some_datetime_column_name AS SIGNED) FROM table_name;| Original Date Value | Value after CAST as SIGNED INTEGER |
|---|---|
| 2018-12-20 05:18:48 | 20181220051848 |
| 2019-03-14 09:11:44 | 20190314091144 |
| 2019-03-21 09:12:36 | 20190321091236 |
| 2019-06-06 06:47:16 | 20190606064716 |
Cast DECIMAL as Integer
If a Decimal is converted to an integer then you will get the integer part of it and it will be rounded to the previous or next integer depending on the digit after the decimal point. Same for Float.
SELECT CAST(some_decimal_column_name AS SIGNED) FROM table_name;| Original Decimal Value | Value after CAST as SIGNED INTEGER |
|---|---|
| 0 | 0 |
| 44375 | 44375 |
| 44375.02988 | 44375 |
| -44375.0298 | 44375 |
| 0.338018116 | 0 |
| 26.66557312 | 27 |
Cast ENUM as Integer
If ENUM is converted to an integer then you will get the position number of the selected element.
Say the enum is ENUM(‘abc’,’def’,’ghi’,’100test’) and we run a query like the below:
SELECT CAST(some_enum_column_name AS SIGNED) FROM table_name;You will get:
| Original ENUM Value | Value after CAST as SIGNED INTEGER |
|---|---|
| abc | 1 |
| def | 2 |
| ghi | 3 |
| 100test | 4 |
Cast SET as Integer
If SET is cast as an integer then you will get the sum of the position of the selected elements.
Say the set is SET(‘a’,’b’,’c’) and you run a query like the below:
SELECT CAST(some_set_column_name AS SIGNED) FROM table_name;| Original SET Value | Value after CAST as SIGNED INTEGER |
|---|---|
| a | 1 |
| b | 2 |
| c | 3 |
| ab | 3 |
| bc | 5 |
| abc | 7 |
Usage of CAST as SIGNED
So this CAST as SIGNED can be used for the SELECT statement to get data, as we have already seen. But this can also be used in other places.
Let’s take a look at some usage other than selecting a value.
Use CAST as SIGNED in the WHERE clause
This CAST as SIGNED can also be used in a WHERE clause.
SELECT CAST(`column_name ` AS SIGNED) FROM table_name WHERE CAST(`column_name` AS SIGNED) > 100;Use CAST as SIGNED for ORDER BY
CAST as SIGNED can be used for ORDER BY.
SELECT CAST(`column_name` AS SIGNED) FROM table_name ORDER BY CAST(`column_name` AS SIGNED) ASC;Also with an alias.
SELECT CAST(`column_name` AS SIGNED) some_alias FROM table_name ORDER BY some_alias ASC;Use CAST as SIGNED for GROUP BY
You can use CAST as SIGNED for a GROUP BY operations.
SELECT CAST(`column_name` AS SIGNED) FROM table_name GROUP BY CAST(`column_name` AS SIGNED) ASC;Also with an alias.
SELECT CAST(`column_name` AS SIGNED) some_alias FROM table_name GROUP BY some_alias ASC