MySQL concat() and lower() weirdness

Question

Any idea why this works sensibly*:

mysql> select lower('AB100c');
+-----------------+
| lower('AB100c') |
+-----------------+
| ab100c          |
+-----------------+
1 row in set (0.00 sec)

But this doesn't?

mysql> select lower(concat('A', 'B', 100,'C'));
+----------------------------------+
| lower(concat('A', 'B', 100,'C')) |
+----------------------------------+
| AB100C                           |
+----------------------------------+
1 row in set (0.00 sec)

*sensibly = 'the way I think it should work.'

Answer 1

As stated on MySql String functions:

---

LOWER(str)

LOWER() is ineffective when applied to binary strings (BINARY, VARBINARY, BLOB).

---

CONCAT(str1,str2,...)

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.

---

In your code you are passing 100 as a numeric so concat will return a binary string and lower is ineffective when applied to binary strings that's why it's not get converted. If you want to convert you can try this:

select lower(concat('A', 'B', '100','C'));

Answer 2

lower is used to convert STRINGS to lowercase. But your value 100 is considered numeric. If you want to still achieve the result of lower case conversion, you should enclose the number in quotes like this:

select lower(concat('A', 'B', '100','C'));

I've tested this and it works fine.