开发者

Creating a SQL query for matching a varchar field using a given list of words

I am looking for a piece of advice in creating a SQL query. I have a MySQL database with a VARCHAR(255) field called 'sentences'. As you can guess this field contains a sentence of several words. I want to query the 开发者_如何学Pythondatabase to retrieve any combination of a few given words but without any other words.

Here comes an example: let's say I want to get every combination of 'do', 'you', 'like', 'tea'. So I would like to have in my result the sentences: 'do you like tea', 'do you like', or 'like tea' or 'do tea' but not the sentences 'do you like coffee', 'do you fancy tea'

Of course, I could create all possible combinations of words with PHP for example and then create the right SQL query. But I am looking for a way to do so only using SQL operators and the given words, without any other pre-processing.

Thank you very much for any help.


if you want to auto-set search for 'do you like' and 'do you like tea' but not 'do you like coffe', I think it's impossible, because for search engine it's just a string of characters. it doesn't know the difference betwwen 'tea' and 'coffe' other than length (3 vs 5) and character set

PS for searching multiple strings try using fulltext


when you use

WHERE (field LIKE '%do%%like%%tea%')

then your given examples work, but you rely on the given order, so a value "like tea do" would not be covered.

I would guess solution would be to form the query like:

WHERE (field LIKE '%do%') AND (field LIKE '%like') AND (field LIKE '%tea%')
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜