Thread: SELECT woes
I wish to SELECT * FROM testTable WHERE any field contains any word in the string "my name is bill" All I can come up with is this. SELECT * FROM testTable WHERE testfeild LIKE '%my%' OR testfield2 LIKE '%my%' OR testfeild LIKE '%name%' OR testfield2 LIKE'%name%' OR testfeild LIKE '%is%' OR testfield2 LIKE '%is%' OR testfeild LIKE '%bill%' OR testfield2 LIKE '%bill%' Is there a better/more efficient way? Kind regards Kevin -- ______ (_____ \ _____) ) ____ ____ ____ ____ | ____/ / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \____) \_||_| \____) \____) Kevin Waterson Port Macquarie, Australia
Yuck. My suggestion would be to install the tsearch or tsearch2 module, and modify the on-insert trigger to include the contents of all the fields in your table. There are examples in the tsearch readme, but you can set the trigger on multiple columns. Then that query would probably be very fast and would turn into select * from table where master_idx ## 'my|name|is|bill' You can also do more powerful levels of grouping and boolean logic. Jeff On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote: > I wish to SELECT * FROM testTable WHERE any field contains any word in > the > string "my name is bill" > All I can come up with is this. > SELECT * FROM testTable WHERE testfeild LIKE '%my%' OR testfield2 LIKE > '%my%' OR testfeild LIKE '%name%' OR testfield2 LIKE '%name%' OR > testfeild LIKE '%is%' OR testfield2 LIKE '%is%' OR testfeild LIKE > '%bill%' OR testfield2 LIKE '%bill%' > > Is there a better/more efficient way? > > Kind regards > Kevin > > -- > ______ > (_____ \ > _____) ) ____ ____ ____ ____ > | ____/ / _ ) / _ | / ___) / _ ) > | | ( (/ / ( ( | |( (___ ( (/ / > |_| \____) \_||_| \____) \____) > Kevin Waterson > Port Macquarie, Australia > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
This one time, at band camp, Jeffrey Melloy <jmelloy@visualdistortion.org> wrote: > Yuck. > > My suggestion would be to install the tsearch or tsearch2 module, and > modify the on-insert trigger to include the contents of all the fields > in your table. There are examples in the tsearch readme, but you can > set the trigger on multiple columns. Then that query would probably be > very fast and would turn into > select * from table where master_idx ## 'my|name|is|bill' > You can also do more powerful levels of grouping and boolean logic. > Jeff > On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote: Then it would not be compatible with other databases. Kind regards Kevin -- ______ (_____ \ _____) ) ____ ____ ____ ____ | ____/ / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \____) \_||_| \____) \____) Kevin Waterson Port Macquarie, Australia
* Kevin Waterson <kevin@oceania.net> [0952 09:52]: > This one time, at band camp, Jeffrey Melloy <jmelloy@visualdistortion.org> wrote: > > > Yuck. > > > > My suggestion would be to install the tsearch or tsearch2 module, and > > modify the on-insert trigger to include the contents of all the fields > > in your table. There are examples in the tsearch readme, but you can > > set the trigger on multiple columns. Then that query would probably be > > very fast and would turn into > > select * from table where master_idx ## 'my|name|is|bill' > > You can also do more powerful levels of grouping and boolean logic. > > Jeff > > On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote: > > Then it would not be compatible with other databases. True, but I don't think there is an standard SQL way of doing what you want. The alternative would be to fidn all rows that contain 'bill' and then have your application dump the ones that don't contain the other words - that would be more portable but is really clanky, and adds complexity to the client. -- An idea is not responsible for the people who believe in it. Rasputin :: Jack of All Trades - Master of Nuns
On Tuesday, September 2, 2003, at 04:58 AM, Kevin Waterson wrote: > This one time, at band camp, Jeffrey Melloy > <jmelloy@visualdistortion.org> wrote: > >> Yuck. >> >> My suggestion would be to install the tsearch or tsearch2 module, and >> modify the on-insert trigger to include the contents of all the fields >> in your table. There are examples in the tsearch readme, but you can >> set the trigger on multiple columns. Then that query would probably >> be >> very fast and would turn into >> select * from table where master_idx ## 'my|name|is|bill' >> You can also do more powerful levels of grouping and boolean logic. >> Jeff >> On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote: > > Then it would not be compatible with other databases. > You didn't list that as a constraint. :) > Kind regards > Kevin > > > -- > ______ > (_____ \ > _____) ) ____ ____ ____ ____ > | ____/ / _ ) / _ | / ___) / _ ) > | | ( (/ / ( ( | |( (___ ( (/ / > |_| \____) \_||_| \____) \____) > Kevin Waterson > Port Macquarie, Australia > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly