Case sensitive for some reason.

[Slarti42]

I'm having a little trouble with a SELECT query. I keep reading here that a query like:

select countyname from tblcounties where countyname like 'N%'

should return the same results as:

select countyname from tblcounties where countyname like 'n%'

BUT with the first one I get all of the results from the table I want and the second one returns nothing. All of the values in the table hav a capital at the begining.

Anyone know what I'm doing wrong??

[freddydoesphp]

Thats weird I just tried it and it worked the same for me whether I used a capital letter or not. I tried

select * from tablename where field1 like 'T%';

//Which produces the exact same result as 

select * from tablename where field1 like 't%'; 

What version of MySQL are you using?

[zook]

County names are capitalized, therfor when the N is capital, you get your results, but when it's not, you don't. The reason for this could be that you are on a Unix server, which is case sensetive for just about everything.

[freddydoesphp]

Except for data in a database, yes table names and db names are case-sensitive, but data should not be. Zook, try it on a unix server if you have access and you too will see that within queries data is case-insensitive. There is some other reason why its not working.

[Slarti42]

I'm running Windows 2k pro and MySQL version 3.23.33 (I think)
The table has two coloms:
1:CountyID - Auto Number - Primary Key
2:CountyName - Blob - Unique - Keyname name

I needed to make the County name unique so that people could not add another county with the same name. Would any of this cause my problem?

[freddydoesphp]

No but the fact that you made the field a blob field is what is causing the case sensitivity. blob is for storing binary data and is case sensitive, all you need is a varchar field for your county name, and your problem should dissappear.

[Slarti42]

I've just checked another table and it's case insensitive . I think this must be something to with the key to make it unique. I've also noticed that if you add a value with lower case at the start, then sort the result set, it gets displayed at the bottom of the list even if it starts with 'a'.

Anyone got any ideas for script to avoid duplicate entries instead of the database doing it??

[Slarti42]

Thank you SO much freddydoesphp. Thats sorted it. I dont know how that managed to change to blob because I created it the same as another table I have in the database. I'm glad I know about that now. Cheers!!!