Thread: How to search for a subString

How to search for a subString

From
b t
Date:
Hi, I'm building a database for a school project and I want to search for the name of the movie,  fro example in the database there is a table called movie and has a movie call "The fast and the furious" so if the type in "the fast" then it should return that movie. I tried using like and ilike and it still would not work.
Thanks in advance
-BT


Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'

Re: [despammed] How to search for a subString

From
Kretschmer Andreas
Date:
am  Sun, dem 06.02.2005, um  2:12:46 -0800 mailte b t folgendes:
> Hi, I'm building a database for a school project and I want to search for the
> name of the movie,  fro example in the database there is a table called movie
> and has a movie call "The fast and the furious" so if the type in "the fast"
> then it should return that movie. I tried using like and ilike and it still

select .. ~ '*the fast*' ...


Regards, Andreas
-- 
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)


Re: [despammed] How to search for a subString

From
Bruno Wolff III
Date:
On Sun, Feb 06, 2005 at 11:45:20 +0100, Kretschmer Andreas <andreas_kretschmer@despammed.com> wrote:
> am  Sun, dem 06.02.2005, um  2:12:46 -0800 mailte b t folgendes:
> > Hi, I'm building a database for a school project and I want to search for the
> > name of the movie,  fro example in the database there is a table called movie
> > and has a movie call "The fast and the furious" so if the type in "the fast"
> > then it should return that movie. I tried using like and ilike and it still
> 
> select .. ~ '*the fast*' ...

That should be:
select .. ~ '.*the fast.*' ...

As REs are unanchored, you can simplify this to:
select .. ~ 'the fast' ...

However, ilike should have worked if it was used properly.

Also note that these searches are going to be slow, since they won't
be able to use an index. If you have so many movies that the speed of
the search is unacceptably slow, then you might want to look at the
tsearch2 contrib module.


Re: [despammed] How to search for a subString

From
Frank Finner
Date:
Do REs automatically ignore case? Otherwise it is clear that ~ 'the
fast' cannot find 'The fast and the furious'. The search
expressions should be all uppercased or lowercased then.

Regards, Frank.

On Sun, 6 Feb 2005 07:32:20 -0600 Bruno Wolff III <bruno@wolff.to>
thought long, then sat down and wrote:

> As REs are unanchored, you can simplify this to:
> select .. ~ 'the fast' ...
>
> However, ilike should have worked if it was used properly.
-- 
Frank Finner
Köpfchenstraße 36
57072 Siegen

Re: [despammed] How to search for a subString

From
Kretschmer Andreas
Date:
am  Sun, dem 06.02.2005, um 16:03:48 +0100 mailte Frank Finner folgendes:
> Do REs automatically ignore case? Otherwise it is clear that ~ 'the

No.


> fast' cannot find 'The fast and the furious'. The search
> expressions should be all uppercased or lowercased then.

Correct.

~  : Case-sensitive
~* : ignore case

!~ and !~* are negative


Regards, Andreas
-- 
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung.   Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org)     GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)


Re: How to search for a subString

From
"Darko Prenosil"
Date:
You probably did not use correct syntax for LIKE, try with
SELECT * FROM movie WHERE name LIKE '%the%fast%';
 
In the future if You want help from this list, be more specific about the problem, send some example, error message or such, not just explanation: "It does not work !"
 
Regards !
 
----- Original Message -----
From: b t
Sent: Sunday, February 06, 2005 11:12 AM
Subject: [INTERFACES] How to search for a subString

Hi, I'm building a database for a school project and I want to search for the name of the movie,  fro example in the database there is a table called movie and has a movie call "The fast and the furious" so if the type in "the fast" then it should return that movie. I tried using like and ilike and it still would not work.
Thanks in advance
-BT


Do you Yahoo!?
Yahoo! Search presents - Jib Jab's 'Second Term'

Re: [despammed] How to search for a subString

From
Bruno Wolff III
Date:
On Sun, Feb 06, 2005 at 16:03:48 +0100, Frank Finner <postgresql@finner.de> wrote:
> Do REs automatically ignore case? Otherwise it is clear that ~ 'the
> fast' cannot find 'The fast and the furious'. The search
> expressions should be all uppercased or lowercased then.

No they don't. I missed that when following up to the previous suggestion.
You need to use the ~* operator instead of ~ to get case insensitive
regular expressions.