frustrated with lcase

[james438]

Hi, I am using mysql 5.0.67 and am using the following to find the made up term terewS


SELECT ID, summary, category FROM news WHERE (lcase(concat(ID, summary, category)) LIKE '%terews%')
the above code does not work, but the following does:

SELECT ID, summary, category FROM news WHERE (lcase(concat(ID, summary, category)) LIKE '%terewS%')

Anyone know what I am doing wrong? I recently ported all of my data over from my MySQL database 4.0 to 5.0.

The code does work with the 4.0 database. I am looking into it. If I figure out the answer before anyone else does I will post a reply with the solution.

[james438]

got it. The problem was with concat, not lcase.


CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary 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); ref (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat)

In other words to fix this I would need to convert my argument to
SELECT ID, summary, category FROM news WHERE lcase(concat(cast(ID as char),summary,category)) LIKE '%terews%'

The original argument I was using should not have been working, but possibly that was by design prior to MySQL 5.0 series.