Thread: Case insensitive "contains" search
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
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
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 > > > > >
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.
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
~~~~~~~~~~~~~~~~~~~~
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
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
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