By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,011 Members | 1,000 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,011 IT Pros & Developers. It's quick & easy.

CONCAT with IF

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 / jt@jt.invalid |
| 2 | lt / lisa tailor / lt@jt.invalid (NOT YET ACTIVATED) |
+--------+------------------------------------------------------+
2 rows in set (0.00 sec)
Nov 23 '05 #2

P: n/a
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.
Nov 23 '05 #3

P: n/a

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

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.