MySQL Forums
Forum List  »  PHP

Re: like (non-case-sensitive)
Posted by: Ulf Wendel
Date: May 05, 2006 08:49AM

Andrew Vandever wrote:
> I'm trying to set up a search function in a

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

> PHP-based, blog-like app, and want to make the
> search not be case-sensitive. The line currently
> reads:

Case-sensitive or not is a matter of the collations you use, here's a simple example to demonstrate the use of a case-sensitive collation (latin1_bin) and a case-insensitive collations (latin1_german1_ci)

mysql> select 'ulf' like 'Ulf' COLLATE latin1_bin;
+-------------------------------------+
| 'ulf' like 'Ulf' COLLATE latin1_bin |
+-------------------------------------+
| 0 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select 'ulf' like 'Ulf' COLLATE latin1_german1_ci;
+--------------------------------------------+
| 'ulf' like 'Ulf' COLLATE latin1_german1_ci |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
1 row in set (0.00 sec)


> WHERE pst_sbjt regexp 'SearchString' OR pst_cont
> regexp 'SearchString'
> I tried before:
> WHERE pst_sbjt like'%SearchString%' OR pst_cont
> like'%SearchString%'
> both return case-sensitive results. How do I make
> it non-case-sensitive?

Change the collation used for the comparison. Which one gets used? Please check the chapter 10 - Character sets - of the manual, http://dev.mysql.com/doc/refman/5.0/en/charset.html .

> Is there a reference somewhere for all the %-like
> modifiers available?

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

BTW, the a query such as "%pattern" or "_pattern" can't make use of an index.

Ulf

Options: ReplyQuote


Subject
Written By
Posted
Re: like (non-case-sensitive)
May 05, 2006 08:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.