MySQL case sensitivity

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

MySQL case sensitivity

Post by mjseaden »

If I have a table containing UK town names, e.g. 'London', and I search a table looking for SELECT.....WHERE Town='london', should MySQL return a hit? Or is it case sensitive? If it is, is there any way I can make it non-case sensitive?

Many thanks

Mark
mjseaden
Forum Contributor
Posts: 458
Joined: Wed Mar 17, 2004 5:49 am

Post by mjseaden »

On UNIX-based operating systems (for instance Linux, which I'm using), apparently searches are case-sensitive, but this isn't the case for Windows-based operating systems (which I was using until a few days ago).

The solution is to use the MySQL LOWER() function (could equally use UPPER()), which I assume could be done like this:

Code: Select all

$query = 'SELECT LOWER(TownName) FROM Towns WHERE TownName='.strtolower('lOnDoN');
Would this work?

Many thanks

Mark
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

Your searching should be case insensitive (OS regardless), the only thing that should be case sensitive on a UNIX-based OS are your Database and Table names. The actual search terms should be fine, however case sensitivity may effect some functions in the SELECT statement like if you are using regular expressions.

Blob fields however are case-sensitive in their search, but you should not be using a blob field to store a town name?
Post Reply