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

e

Updated on: 30-Jul-2019

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started