Re: [GENERAL] Case insensitive "contains" search

At 11:30 +0200 on 9/11/98, Wim Ceulemans 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?

Are you quite sure about that? That it finds a string in which there is no
'test' even as part of the word? Because that would indicate a bug in the
regexp algorithm, and it certainly doesn't happen in my version of Postgres
(6.2.1):

testing=> select * from test2;
t
---------------------------------------------------------------
Once upon a midnight dreary
Whether tempter sent or whether tempest tossed thee here ashore
This is a mere test line
Survival of the fittest is the Jungle Law.
Testimony does not contain it in case-sensitive.

(6 rows)

testing=> select * from test2 where t ~* 'test';
t
------------------------------------------------
This is a mere test line
Survival of the fittest is the Jungle Law.
Testimony does not contain it in case-sensitive.
(3 rows)

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma