Re: [GENERAL] Case insensitive "contains" search

[Ulf Mehlig <umehlig(at)uni-bremen(dot)de>]

Wim Ceulemans <wim(dot)ceulemans(at)pophost(dot)eunet(dot)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(at)zmt(dot)uni-bremen(dot)de>
   %%%%!%%%       Projekt "MADAM"         <umehlig(at)uni-bremen(dot)de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |  
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~