CONCAT with IF

[nickdevx]

Damn it this "illegal mix of collashit" messages are driving me nuts!!

What's wrong with the following statement?

SELECT userid, CONCAT(username,' / ',firstname,' ',lastname,' / ',
email, IF(activated=1,'',' (NOT YET ACTIVATED)')) AS uname FROM tbuser

I get:

MySQL Error Occured
1271: Illegal mix of collations for operation 'concat'

Same error if I try CAST'ing "activated" to CHAR

SELECT userid, CONCAT(username,' / ',firstname,' ',lastname,' / ',
email, IF(CAST(activated AS CHAR)='1','',' (NOT YET ACTIVATED)')) AS
uname FROM tbuser

[Aggro]

ni******@hotmail.com wrote:

Damn it this "illegal mix of collashit" messages are driving me nuts!!

What's wrong with the following statement?

SELECT userid, CONCAT(username,' / ',firstname,' ',lastname,' / ',
email, IF(activated=1,'',' (NOT YET ACTIVATED)')) AS uname FROM tbuser

Tested with MySQL version 4.0, it worked fine.
mysql> create table tbuser ( userid int, username text, firstname text,
lastname text, email text, activated int );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tbuser values(1,'jt','jack','tailor','j*@jt.invalid',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbuser values(2,'lt','lisa','tailor','l*@jt.invalid',0);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT userid, CONCAT(username,' / ',firstname,' ',lastname,' / ',
-> email, IF(activated=1,'',' (NOT YET ACTIVATED)')) AS uname FROM
tbuser;
+--------+------------------------------------------------------+
| userid | uname |
+--------+------------------------------------------------------+
| 1 | jt / jack tailor / [e-mail] |
| 2 | lt / lisa tailor / [e-mail] (NOT YET ACTIVATED) |
+--------+------------------------------------------------------+
2 rows in set (0.00 sec)

[Bill Karwin]

ni******@hotmail.com wrote:

MySQL Error Occured
1271: Illegal mix of collations for operation 'concat'

Just a guess, but I'd try using the BINARY operator to cast your char
fields to binary strings, so that the arguments of CONCAT are compatible.

See http://dev.mysql.com/doc/refman/5.0/...functions.html

I'm not sure I can offer specific usage suggestions, I'm not experienced
with character sets and collations and stuff. See what you can learn
from the docs.

Regards,
Bill K.

[nickdevx]

Bill Karwin wrote:

ni******@hotmail.com wrote:

MySQL Error Occured
1271: Illegal mix of collations for operation 'concat'

Just a guess, but I'd try using the BINARY operator to cast your char
fields to binary strings, so that the arguments of CONCAT are compatible.

See http://dev.mysql.com/doc/refman/5.0/...functions.html

I'm not sure I can offer specific usage suggestions, I'm not experienced
with character sets and collations and stuff. See what you can learn
from the docs.

Regards,
Bill K.

Well what do ya know... I did try BINARY without working but
re-arraging fix it:

SELECT userid, CONCAT(username,' / ',firstname,' ',lastname,' / ',
email, CAST(IF(activated=1,'', (NOT YET ACTIVATED)') AS BINARY)) AS
uname FROM tbuser