Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

CAST() seems to only work for BINARY,CHAR,DATE;DATETIME,DECIMAL,TIME,SIGNED,UNSIGNED.

I need to convert a hex string to a bigint, that is, I'd want:

SELECT CAST(CONV("55244A5562C5566354',16,10) AS BIGINT)

CONV() returns a string, so that's why I'm trying the convert it. I have 2 uses for this

  • Inserting data, e.g. INSERT INTO a(foo) SELECT CONV(bar,16,10) FROM ... Here foo is a bigint column, bar a varchar. Perhaps I could get away with the select statement being a string and let MySQL take care of it (?)

  • Returning data where the client will dynamically learn the data type of the column, SELECT CONV(bar,16,10) is no good as the client will handle it as a string.

like image 417
Anonym Avatar asked Jan 11 '11 17:01

Anonym


People also ask

How do I CAST datatype in MySQL?

The CAST() function in MySQL is used to convert a value from one data type to another data type specified in the expression. It is mostly used with WHERE, HAVING, and JOIN clauses. This function is similar to the CONVERT() function in MySQL. It converts the value into DATE datatype in the "YYYY-MM-DD" format.

What is CAST () in MySQL?

The CAST() function converts a value (of any type) into the specified datatype.

How do you CAST BIGINT to string?

toString() method returns the decimal String representation of this BigInteger. This method is useful to convert BigInteger to String. One can apply all string operation on BigInteger after applying toString() on BigInteger. Return Value: This method returns decimal String representation of this BigInteger.

Does MySQL support BIGINT?

MySQL supports the SQL standard integer types INTEGER (or INT ) and SMALLINT . As an extension to the standard, MySQL also supports the integer types TINYINT , MEDIUMINT , and BIGINT .


2 Answers

SELECT CAST(CONV('55244A5562C5566354',16,10) AS UNSIGNED INTEGER);
like image 189
Quassnoi Avatar answered Sep 23 '22 01:09

Quassnoi


What seems to be the problem? I've tested this conversion both on 64-bit and 32-bit system. Works fine. Note, that instead of doing hex to bin conversion, you can just treat the number as hexadecimal.

mysql> SELECT CAST(X'55244A5562C5566354' AS UNSIGNED);
+-----------------------------------------+
| CAST(X'55244A5562C5566354' AS UNSIGNED) |
+-----------------------------------------+
|                     2614996416347923284 |
+-----------------------------------------+
1 row in set (0.00 sec)
like image 42
vartec Avatar answered Sep 23 '22 01:09

vartec