How to Make a Select in PHP/MySQL Case Insensitive

How to make a SELECT in PHP/MySQL case insensitive?

you can use LIKE:

WHERE foo LIKE 'bar'

Or you can cast both lowercase with:

WHERE LOWER(foo) = LOWER("bar")

The example with LOWER() is most effective where you know that all of your data in the database is already lower cased and then you can just execute:

WHERE foo = LOWER("bar")

This would be a cheaper comparison than the LIKE if you can lower case all of the data in your database.

Php MySQL case sensitive

I don't know if it's me but I'm not sure to understand your question.

If all you want is to be sure to find the items you want regardless of the case, then you can probably use MySQL functions named UPPER(...) and LOWER(...) that allow you to convert a string in uppercase or lowercase respectively

For example :

SELECT name FROM product_description WHERE LOWER(name) = 'squirl';

Will match both Squirl, SQUIRL or squirl

PHP Mysql Case insensitive table select

Thanks for the answers but it seems I've answered my own question. I'm just going to store all tables as lowercase using strtolower()

And then:

$LowerName = strtolower($name);
if( mysql_num_rows( mysql_query("SHOW TABLES LIKE '$LowerName'")) == 1)
{
   ....
}

PHP/Mysql Search - Case sensitive

You can try:

$search_word_fix=strtolower(str_replace(" ","%",$search_word_new));
$sql=mysql_query("SELECT * FROM tweets WHERE lower(content) LIKE '%$search_word_fix%' ORDER BY votes DESC LIMIT 20");

• I've added strtolower to make
  $search_word_fix all lower case.
• And in the where clause I've changed
  content to lower(content) so that
  I compare with lowercase of
  content.

You could have made both these changes in the query as suggested in other answer.

Select case insensitive using mysql, php and pdo

This

...snip... ) LIKE '%' LOWER(:wildcard) '%' OR ...snip

is incorrect. You've got a string ('%') followed by a function call (LOWER()) followed by another string, and they're just sitting there - no connecting logic, no concatenation, blah blah blah .

It should be

... LIKE CONCAT('%', LOWER(:wildcard), '%') OR ...

And by default, mysql comparisons ARE case insensitive, unless you force a binary comparison, or you're using a case sensitive collation on your db/table.

How to make a 'select' with case-sensitive 'like' in MySQL?

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

MySQL :: MySQL 5.6 Reference Manual :: C.5.5.1 Case Sensitivity in String Searches (emphasis added)

---

Related Topics