Thread: 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'
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° ;-)
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.
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
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° ;-)
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 tSent: Sunday, February 06, 2005 11:12 AMSubject: [INTERFACES] How to search for a subStringHi, 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'
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.