why is this case sensitive?

[james438]

SELECT ID, ID, summary FROM memoblog WHERE ( concat(ID, summary) LIKE '%wordpress%' ) will produce 6 results.


SELECT ID, ID, summary FROM memoblog WHERE ( concat(ID, summary) LIKE '%Wordpress%' ) will produce 2 results.

How can I get this to be case insensitive? I am using MySQL 5.0 and 4.0.

[james438]

got it.

I can make the search terms lowercase. Then I can execute the query like
SELECT ID, ID, summary FROM memoblog WHERE (lcase( concat(ID, summary)) LIKE '%wordpress%' ) That will look at all of the contents in the database as lowercase as well.

I am now getting 8 results :)

problem solved.

[james438]

After looking a bit more it seems that the reason has to do with the character set used. some are case sensitive and some are not. The problem is that since I primarily use version 4.0 it is a bit more difficult to check exactly which version is being used.

SHOW VARIABLES
SHOW TABLE STATUS
SHOW STATUS

all of the above do not give me the information that I need. I have not looked too closely, but MySQL 5.0 appears to have the info available.

Anyway, using SHOW VARIABLES I was able to discover that I am using character set latin1. Since there are several types of latin my guess is that I am using latin1_general_cs which is the only case sensitive one listed. http://dev.mysql.com/doc/refman/4.1/en/charset-mysql.html

This thread was mildly helpful http://forums.mysql.com/read.php?10,204607,204607#msg-204607

I am not a big fan of the forum that mysql uses. There are far better ones out there that are easier to navigate and faster to use.