Showing posts with label decimal. Show all posts
Showing posts with label decimal. Show all posts

Friday, October 28, 2022

MySQL casting from decimal to string - mysql cast to varchar - mysql convert decimal to string char varchar

MySQL - casting from decimal to string

select CAST(ROUND(345345345345345345345353453453453454.4345345,6) AS CHAR(65)) AS 'big_number';

don't try to cast to varchar, there is a bug at MySQL end

Monday, March 6, 2017

Illegal mix of collations for operation 'UNION'

I don't know for how many reason this error occurred but I found it when going to create a view using two tables using UNION. 

There are different ways to solve this problem. You can solve it by compress the value and then decompress as UNCOMPRESS(COMPRESS(x.name)) AS name.

You have another way to fix this problem. You can use first hex the value and then unhex as UNHEX(HEX(x.name)) AS name.

And finally, to fix this, you need to replace some column references in the SELECT list (in one or more of the queries) with an expression, something like CONVERT(name USING UTF8) AS name.

Some more convert functions are listed below:
CONVERT('2014-02-28', DATE)
CONVERT('2014-02-28 08:14:57', DATETIME)
CONVERT('08:14:57', TIME)
CONVERT(125, CHAR)
CONVERT(4-6, SIGNED)
CONVERT(4-6, UNSIGNED)
CONVERT('4', BINARY)
CONVERT('Some String' USING UTF8)
CONVERT('Some String' USING ASCII)
CONVERT('Some String' USING LATIN1)
CONVERT(x.price, DECIMAL)
CONVERT(x.price, DECIMAL(10,2))

Wednesday, September 4, 2013

MySql cast or convert data(integer, float, double, decimal, date, etc...) as character/string

Fiddle link

You will need to cast or convert as a CHAR datatype, there is no varchar datatype that you can cast/convert data to:
select CAST(id as CHAR(50)) as col1 from t9;

select CONVERT(id, CHAR(50)) as colI1 from t9;
select CAST(amount AS DECIMAL(10, 2)) AS amount FROM t9;