no results in WHERE LIKE without capital

[djpaul]

Hello!
I have this query but it only works if the first letter is a capital.

This is the query:

SELECT id, companyname, city FROM crediteuren 
WHERE ( companyname LIKE '%Someon%' ) OR (city LIKE '%Someon%' )
ORDER BY companyname

And this doesn't work....:

SELECT id, companyname, city FROM crediteuren 
WHERE ( companyname LIKE '%someon%' ) OR (city LIKE '%someon%' )
ORDER BY companyname

I realy don't get it....
Thanks in advance!

Paul

[code green]

The fields in question are not CHAR, VARCHAR or TEXT.
Or it is the collation - using a character set which is case sensitive.
I don't fully understand collation but I always makes sure table and fields are latin1

[mwasif]

It looks like you are using case sensitive collation. Provide the output of the following query so that we can suggest you something

DESC crediteuren;

[nathj]

If it is case sesitivity and you want to maintain that then you could use something like:

    SELECT id, companyname, city FROM crediteuren 
    WHERE ( LOWER(companyname) LIKE '%someon%' ) OR (LOWER(city) LIKE '%someon%' )
    ORDER BY companyname
 

There is also an UPPER() function that could be used.

Hope that helps
nathj

[djpaul]

Well, it was the collation.
1 column was set to latin and the other was set to (i think is was..) latin1_ch
Changed to latin1 and it worked!

Thanks!

[Atli]

@nathj

You can also just specify which COLLATE to use, instead of manipulating the string like that.

SELECT ...
WHERE col1 LIKE '%something%' COLLATE latin1_general_ci
OR    col2 LIKE '%something%' COLLATE latin1_general_cs;
 

There the first column would be search using the case-insensitive latin1 collation, but the second one using the case-sensitive version.