Re: [GENERAL] Case insensitive "contains" search - Mailing list pgsql-general

From Postgres DBA
Subject Re: [GENERAL] Case insensitive "contains" search
Date
Msg-id Pine.BSF.4.02.9811091216320.1065-100000@nest.bistbn.com
Whole thread Raw
In response to Case insensitive "contains" search  ("Wim Ceulemans" <wim.ceulemans@pophost.eunet.be>)
List pgsql-general
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
>
>
>
>
>


pgsql-general by date:

Previous
From: Mike Meyer
Date:
Subject: 6.5 and FreeBSD 3.0-RELEASE?
Next
From: Oleg Broytmann
Date:
Subject: Re: [GENERAL] Case insensitive "contains" search