Re: [GENERAL] Case insensitive "contains" search

hi!
I'm not shure that syntax you have used is correct, I mean that better is:
select * from tablex where name LIKE '%test%';
or you can also try "~~" operator that is used in Postgres as synonym to
"LIKE":
select * from tablex where name ~~ '%test%';

Regarding your second example:
select * from tablex where name ~* 'test';
It SHOULDN'T return strings with name a la "tempest"!
It means that either you mistyped the query, or there some troubles with
your RegularExpession library.

Aleksey.

On Mon, 9 Nov 1998, Wim Ceulemans wrote:

> Hi
>
> A 'contains' search in postgreSQL is:
>
> select * from tablex where name = '%test%';
>
> When I want exactly the same but case insensitive, how do I do that? By
> searching through the mail archive I came up with:
>
> 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?
>
>
> Regards
>
> Wim Ceulemans
> Nice Software Solutions
> Eglegemweg 3, 2811 Hombeek - Belgium
> Tel +32(0)15 41 29 53 - Fax +32(0)15 41 29 54
>
>
>
>
>