[SOLVED] Query with LIKE is still case sensitive

[centerwork]

Here is my problem, the query below searches though my customer database. The only problem is that if the cases don't match it will not find them.

 

I was under the impression that LIKE was suppose to ignore character cases.

 

$query = "select *,CONCAT(c_id, c_user, c_name, c_b_addr, c_b_city, c_b_state, c_b_zip, c_b_first, c_b_last, c_b_phone) AS alais1 FROM customers HAVING alais1 LIKE '%$trimmed%'";

[Hooker]

This should help (find your version of mysql in the left menu for revelvant doc's):

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

[centerwork]

I read thought that and tryed altering my tables COLLATE. and adding COLLATE latin1_general_cs  to my query as it suggest but it is still not working. I might be missing something I'm kinda tired.

[Hooker]

Give this a try (its un-tested):

$query = "SELECT * FROM customers WHERE LOWER(CONCAT_WS(' ',c_id, c_user, c_name, c_b_addr, c_b_city, c_b_state, c_b_zip, c_b_first, c_b_last, c_b_phone)) LIKE LOWER('%$trimmed%')";

[PFMaBiSmAd]

If any of your columns in the CONCAT() are binary, the result of CONCAT() will be binary and the comparison will be case sensitive -

 

CONCAT(str1,str2,...)

 

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

 

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

 

CONCAT() returns NULL if any argument is NULL.

 

mysql> SELECT CONCAT('My', 'S', 'QL');

        -> 'MySQL'

mysql> SELECT CONCAT('My', NULL, 'QL');

        -> NULL

mysql> SELECT CONCAT(14.3);

        -> '14.3'

 

You would need to CAST() any binary columns are CHAR for the result to be non-binary so the comparison would be case-insensitive.

 

 

[centerwork]

Thank you, It took me a minite to figure it out.

 

Short and sweet. I had one field that was a INT, and if I understany you write if there is one INT in the CONCAT() all the field in the CONCAT() will become binary string. Which means they are case sensitive.

 

Here is the new query. IT WORK GREAT!

 

$query = "select *,CONCAT(CAST(c_id AS CHAR), c_user, c_name, c_b_addr, c_b_city, c_b_state, c_b_zip, c_b_first, c_b_last, c_b_phone) AS alais1 FROM customers HAVING alais1 LIKE '%$trimmed%'";

 

Thank you all for your help.