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