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