user2355051 user2355051 - 13 days ago 6
MySQL Question

Make Mysql Query Case Insentive

Is it possible to make the following query string case insensitive?

SELECT SUBSTR(raw,INSTR(raw,'".$term."') - 50,350) as term,
(LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."',''))) /LENGTH('".$term."') AS occur,name,title
FROM (
SELECT ( LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."','')) ) / LENGTH('".$term."') AS occur, raw, consol.name,title
FROM consol
WHERE LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."',''))>0 AND raw LIKE '%".$term."%' COLLATE utf8_general_ci ORDER BY occur DESC
) t ";


The engine for the table is MyISAM and the encoding is UTF8. When i run the query from the command line it works, but when I execute it through the browser it throws the following error.


Notice: failed Query : COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' in


The table is encoded in UTF-8 with the collation of utf8_general_ci. How can I ensure that a query of "small" matches small as well as Small or SMALL ?

Answer

It appears as if the WHERE clause in the second select statement was causing the issue. I changed this:

WHERE LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."',''))>0 AND raw LIKE '%".$term."%' COLLATE utf8_general_ci ORDER BY occur DESC

To this:

WHERE raw LIKE '%".$term."%' ORDER BY occur DESC

It appears to match all results now. The initial query filters matches from the second query. It looks like the WHERE condition of LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."',''))>0 was causing the issue as well as the setting of the collation.