Clever Geek Handbook

Mysql select concat (charfield, format (doublefield, 8)) gives error 1267

which is ironic on two points, 1) since concat (charfield, doublefield) works (it doesn't matter when one of the fields to be concatenated is numeric) and 2) because the mysql link shows this: CONCAT (str1, str2, .. .) as a prototype for CONCAT and FORMAT: "FORMAT (X, D) Formats number X in format like" #, ###, ###. ## ", rounded to D decimal places and returns the result as a string .

Desperate I tried select concat(symbol, cast(format(closeprice,8) as char))

which worked. So you might be thinking, "Why is this guy wasting our time, he found something that works," which is true. But it doesn't make sense to me and so I was wondering if someone could clarify it?

+2


source to share


3 answers


1267 is an illegal combination of sorts. The sorting (alphabetical sorting) of the charfield and the sorting of the string returned from FORMAT (perhaps your db server default sort) are different.



Change either column collation or server default collation or do a cast.

+4


source


I had the same problem today and this is what worked for me:

Query portion:

CONCAT(payment_currency," ", CONVERT(FORMAT(payment_amount, 2) using utf8)) AS payment_gross_amount


Result:

PHP 1,250.00

Just in case someone else is facing the same problem. :)

+1


source


Try:

select concat(symbol, CONVERT(cast(format(closeprice,8) as char) USING utf8) COLLATE utf8_bin))

As you cannot mix encoding encoding in a CONCAT function like CONCAT (utf8_general_ci, utf8_bin).

0


source






More articles:


All Articles