List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:August 25 2003 6:38pm
Subject:Re: simple query
View as plain text  
* Pag
> 	Imagine i have a table "phrases" with a field "ph" with
> contents like these:
>
> 	- who was it
> 	- who wasnt it
> 	- no i didnt
> 	- yes i was
> 	- dont know who
>
> 	I want to make a SELECT that gives me only the entries that
> have the word "who":
>
> 	Something like
>
> 	SELECT * FROM `phrases` WHERE ph="who*";
>
> 	I tried the manual but cant make sense of it. How can we
> use wildcards on selects?

See the LIKE operator:

<URL: http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1234 >
<URL: http://www.mysql.com/doc/en/MySQL_indexes.html#IDX905 >

SELECT * FROM `phrases` WHERE ph LIKE "who%";

However, this would not return your last example, "dont know who". To match
any occurences of "who", you can use this:

SELECT * FROM `phrases` WHERE ph LIKE "%who%";

Note that an index can not be used in this case, making it slower when you
have a lot of data. Another problem is words containing other words: the
last SELECT statement would also match "knowhow".

To only match the word "who", you could try something like this:

SELECT * FROM `phrases` WHERE
  ph = "who" OR
  ph LIKE "who %" OR
  ph LIKE "% who" OR
  ph LIKE "% who %";

This statement would however not find this value: "who, if any".

You could take a look at regular expressions:

<URL: http://www.mysql.com/doc/en/String_comparison_functions.html#IDX1239 >

And finally, the FULLTEXT feature could possibly be of use for you:

<URL: http://www.mysql.com/doc/en/Fulltext_Search.html >

HTH,

--
Roger

Thread
simple queryPag25 Aug
  • RE: simple queryAllen Weeks25 Aug
  • Re: simple queryRoger Baklund25 Aug
  • Re: simple queryJon Haugsand26 Aug
Re: simple queryAMEVANS26 Aug