Re: [GENERAL] Case insensitive "contains" search

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
~~~~~~~~~~~~~~~~~~~~