CODEWITHSUNDEEP
mysqlconcatenation
David Kryzaniak
David Kryzaniak

Reputation: 163

MySQL CONCAT() Returns Unreadable Text

I'm having some problems with the MySQL CONCAT() function. I ran the following:

SELECT CONCAT(now(),now())

And this is what I got back: 

323031342d30352d30352031343a33393a3535323031342d30352d30352031343a33393a3535

Not sure what exactly what is going on? Has anyone seen this before? This happens when concatenating anything (columns, strings, mysql functions like now())

My server version is 5.1.63 - SUSE MySQL RPM" and the client version is libmysql - mysqlnd 5.0.7-dev - 091210 - $Revision: 304625 $

Upvotes: 2

Views: 710

Answers (1)

spencer7593
spencer7593

Reputation: 108420

Looks like a hexadecimal representation of printable ASCII characters:

hex: 32 30 31 34 2d 30 35 2d 30 35 20 31 34 3a 33 39 3a 35 35
char: 2  0  1  4  -  0  5  -  0  5     1  4  :  3   9 :  5  5

I can't explain why the client is displaying character data as hexadecimal; I'd investigate the possibility that there's a mismatch in character set encoding.

Possibly the MySQL client library is using latin1, but the application is using a different encoding; but we'd expect this would affect all character expressions, not just CONCAT() expressions.

Actually, it's more likely the client is displaying hexadecimal for binary strings, and the value returned from CONCAT() is being reported as a binary string.

Here is an excerpt from MySQL 5.1 documentation for CONCAT() function:

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

So, the workaround might be to CAST the value of NOW() as character, either using a CAST or possibly using the DATE_FORMAT function, e.g.

CONCAT(DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s'),DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s'))

Upvotes: 5

Related Questions