How do I cast a type to a BigInt in MySQL?

You need to use the CAST operator along with CONV() function. The CONV() function can be used to convert one base number system to another base system.

For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.

The syntax is as follows −

SELECT CAST(CONV('yourColumnName',16,10) AS UNSIGNED INTEGER) as anyAliasName FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table castTypeToBigIntDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,  
   -> Value varchar(100),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.19 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into castTypeToBigIntDemo(Value) values('AB5');
Query OK, 1 row affected (0.13 sec)
mysql> insert into castTypeToBigIntDemo(Value) values('55244A5562C5566354');
Query OK, 1 row affected (0.15 sec)
mysql> insert into castTypeToBigIntDemo(Value) values('4546575765ABD78');
Query OK, 1 row affected (0.15 sec)
mysql> insert into castTypeToBigIntDemo(Value) values('5979787DEFAB');
Query OK, 1 row affected (0.19 sec)
mysql> insert into castTypeToBigIntDemo(Value) values('86868686856ABD');
Query OK, 1 row affected (0.17 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from castTypeToBigIntDemo;

The following is the output −

+----+--------------------+
| Id | Value              |
+----+--------------------+
|  1 | AB5                |
|  2 | 55244A5562C5566354 |
|  3 | 4546575765ABD78    |
|  4 | 5979787DEFAB       |
|  5 | 86868686856ABD     |
+----+--------------------+
5 rows in set (0.00 sec)

Here is the query to cast a type to a BigInt in MySQL −

mysql> SELECT CAST(CONV(Value,16,10) AS UNSIGNED INTEGER) as BigNumber from castTypeToBigIntDemo;

The following is the output −

+----------------------+
| BigNumber            |
+----------------------+
| 2741                 |
| 18446744073709551615 |
| 311985829366644088   |
| 98378247434155       |
| 37865559219858109    |
+----------------------+
5 rows in set, 1 warning (0.00 sec)

Samual Sam

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• What is a type cast in C/C++?
• How do I remove a MySQL database?
• How do I pass a variable to a MySQL script?
• Difference between BIGINT and BIGINT(20) in MySQL?
• How to cast DATETIME as a DATE in MySQL?
• How do I create a view in MySQL?
• C# Program to cast a type to its IEnumerable equivalent
• How do I add more members to my ENUM - type column in MySQL?
• How do I add a check constraint to a table in MySQL?
• How to understand if a bigint is signed or unsigned in MySQL?
• How do I insert a NULL value in MySQL?
• How do I exclude a specific record in MySQL?
• How do I drop a primary key in MySQL?
• How do I modify a MySQL column to allow NULL?
• How do I alter a MySQL table column defaults?