Re: [GENERAL] Case insensitive "contains" search

Wim Ceulemans <wim.ceulemans@pophost.eunet.be> wrote

 > select * from tablex where name ~* 'test';
 >
 > But this is not quite the same because it also finds for example
 > 'tempest', whereas the like search does not find it. So is there a
 > postgreSQL operator that does the same as the like %% search but
 > case insensitive?

Does it really find 'tempest' with regular expression 'test'?! This
shouldn't be (and it doesn't work like that on my machine with
v. 6.4):

   => create table xxx (the_text text);
CREATE
   =>  insert into xxx (the_text) values ('tempest');
INSERT 114708 1
   =>  insert into xxx (the_text) values ('test');
INSERT 114709 1
   => select * from xxx where the_text ~* 'test';

the_text
--------
test
(1 row)

You would find both with, e.g.,  'te.*st' (dot stands for any char,
'*' for zero or arbitrarily many of the preceding element):

   => select * from xxx where the_text ~* 'te.*st';

the_text
--------
tempest
test
(2 rows)

In my opinion, the regexps are much more powerfull compared to "like
'%anything'"-statements; but the regular expression syntax is a little
bit weird, and if you have a look in the docs of programs which
heavily depend on regexps (perl, grep, emacs, ...) you'll find some
nice annoying little differences in "to-quote-or-not-to-quote"
sections ...

Anyway, there seem to be quite a lot of introductory texts (including
a whole O'Reilly Book dedicated to Regular Expressions, which I saw in
the book store), maybe it's worth to have a look at them!

Hope it helps,
Ulf

--
======================================================================
 %%%%%            Ulf Mehlig              <umehlig@zmt.uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~