CONCAT with IF

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

Re: CONCAT with IF

nickdevx@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','jt@jt.invalid',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbuser values(2,'lt','lisa','tailor','lt@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 / jt@jt.invalid |
| 2 | lt / lisa tailor / lt@jt.invalid (NOT YET ACTIVATED) |
+--------+-------------------------------------------------- ----+
2 rows in set (0.00 sec)

Re: CONCAT with IF

nickdevx@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/en/cast-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.

Re: CONCAT with IF

Bill Karwin wrote:
> nickdevx@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/en/cast-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