By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

no results in WHERE LIKE without capital

100+
P: 137
Hello!
I have this query but it only works if the first letter is a capital.

This is the query:
Expand|Select|Wrap|Line Numbers
  1. SELECT id, companyname, city FROM crediteuren 
  2. WHERE ( companyname LIKE '%Someon%' ) OR (city LIKE '%Someon%' )
  3. ORDER BY companyname
And this doesn't work....:
Expand|Select|Wrap|Line Numbers
  1. SELECT id, companyname, city FROM crediteuren 
  2. WHERE ( companyname LIKE '%someon%' ) OR (city LIKE '%someon%' )
  3. ORDER BY companyname
I realy don't get it....
Thanks in advance!

Paul
Jun 23 '11 #1
Share this Question
Share on Google+
5 Replies


code green
Expert 100+
P: 1,726
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
Jun 24 '11 #2

mwasif
Expert 100+
P: 801
It looks like you are using case sensitive collation. Provide the output of the following query so that we can suggest you something

Expand|Select|Wrap|Line Numbers
  1. DESC crediteuren;
Jun 24 '11 #3

nathj
Expert 100+
P: 938
If it is case sesitivity and you want to maintain that then you could use something like:
Expand|Select|Wrap|Line Numbers
  1.     SELECT id, companyname, city FROM crediteuren 
  2.     WHERE ( LOWER(companyname) LIKE '%someon%' ) OR (LOWER(city) LIKE '%someon%' )
  3.     ORDER BY companyname
  4.  
There is also an UPPER() function that could be used.

Hope that helps
nathj
Jun 30 '11 #4

100+
P: 137
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!
Jul 8 '11 #5

Atli
Expert 5K+
P: 5,058
@nathj

You can also just specify which COLLATE to use, instead of manipulating the string like that.
Expand|Select|Wrap|Line Numbers
  1. SELECT ...
  2. WHERE col1 LIKE '%something%' COLLATE latin1_general_ci
  3. OR    col2 LIKE '%something%' COLLATE latin1_general_cs;
  4.  
There the first column would be search using the case-insensitive latin1 collation, but the second one using the case-sensitive version.
Jul 8 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.