Thread: Case insensitive "contains" search

Case insensitive "contains" search

From
"Wim Ceulemans"
Date:
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




Re: [GENERAL] Case insensitive "contains" search

From
Herouth Maoz
Date:
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



Re: [GENERAL] Case insensitive "contains" search

From
Postgres DBA
Date:
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
>
>
>
>
>


Re: [GENERAL] Case insensitive "contains" search

From
Oleg Broytmann
Date:
Hi!

On Mon, 9 Nov 1998, Wim Ceulemans wrote:
>
> select * from tablex where name = '%test%';
>
> When I want exactly the same but case insensitive, how do I do that? By

   Convert all to uppercase. Something like
select * from tablex where UPPER(name) = '%TEST%';

Oleg.
----
    Oleg Broytmann  National Research Surgery Centre  http://sun.med.ru/~phd/
           Programmers don't die, they just GOSUB without RETURN.


Re: [GENERAL] Case insensitive "contains" search

From
Ulf Mehlig
Date:
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
  ~~~~~~~~~~~~~~~~~~~~

Re: [GENERAL] Case insensitive "contains" search

From
Charles Curley
Date:

Ulf Mehlig wrote:

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

Is there a text that describes the RE usage in PostgrSQL? I'd like to know
how to do more complicated expressions, like, "^(((charl|jam)es)|(bob))",
which should match a field that begins with "charles", "james", or "bob".
Yes, it can be rewritten, but I have yet to see a demo of how to use
parenthetical RE's in PostgreSQL. What I want to do is provide the user
with the ability to use RE's ala egrep. It would be really sweet to see
perl RE's in there. I've written a couple of times about this and I've
gotten answer as to how to rewrite a particular RE to work with
PostgreSQL, but I would like a more general description. Can the backend
be easily linked with a different regexp library like GNU's? BTW, all of
my comments are referencing 6.3.2.

--
Charles Curley, Staff Engineer
Computer Integrated Manufacturing
Lockheed Martin Ocala Operations



Re: [GENERAL] Case insensitive "contains" search

From
"Gene Selkov Jr."
Date:
Charles Curley wrote:
>
> Ulf Mehlig wrote:
>
> > 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 ...
>
> Is there a text that describes the RE usage in PostgrSQL? I'd like to know
> how to do more complicated expressions,

Things look like POSIX expressions to me. Also looks like the manuals
for regexp were not included because postgres simply links to the regex
library documented elswhere. Try these two sources:

http://tiger8.com/us/regexp-manpage.html (complete but too formal)
http://www.blighty.com/products/spade/help/d_regex.htm (informal but incomplete)

> like, "^(((charl|jam)es)|(bob))",
> which should match a field that begins with "charles", "james", or "bob".

try (((^charl|^jam)es)|(^bob))

--Gene

Python interface and large objects?

From
Mike Meyer
Date:
Does someone have the Python interface handling large objects
properly? I keep winding up with size 0 objects in PostgreSQL 6.3.2.

    Thanx,
    <mike