0

I am trying to make a query where I select all the rows that do not contain a specific word, for this I have a fulltext type index in this column, try the following bolt works:

SELECT *
FROM products
WHERE MATCH(title) AGAINST(' -Dolo' IN BOOLEAN MODE)

So how can I perform this search?

FeRcHo
  • 1,119
  • 5
  • 14
  • 27
  • Does this answer your question? [SQL Query Where Field DOES NOT Contain $x](https://stackoverflow.com/questions/232935/sql-query-where-field-does-not-contain-x) – mitkosoft Jan 08 '20 at 13:50

1 Answers1

1

If I have understood you correctly you want to find all the rows from the table that do not contain a word'Dolo'.

Well you can use NOT operator for that.

SELECT *
FROM products
WHERE NOT MATCH(title) AGAINST('Dolo');

Here is a DEMO.

Also, you can use it like this(because as the OP has asked: "if the whole word is "dolorem", would this query work?"):

SELECT  title as Title
        , MATCH(title) AGAINST('Dolo*' IN BOOLEAN MODE) as Score
FROM products
WHERE MATCH(title) AGAINST('Dolo*' IN BOOLEAN MODE) = 0;

* is a wildcard.

Other signs are described here: https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html

Here is the DEMO for the second example.

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Hi @FeRcHo , so... did I understood you right correctly ? – VBoka Jan 08 '20 at 16:57
  • hello thanks for answering, if that is really what I am looking for, however I have a doubt: this only works for whole words, for example if the whole word is "dolorem", would this query work? – FeRcHo Jan 08 '20 at 17:06
  • If it is Dolorem, what do you want then ? To show it or not ? Also, please note: vote up the answer when the answer is of any help. Mark it as correct when it is exactly what you need. – VBoka Jan 08 '20 at 17:18
  • @FeRcHo , I have updated my answer... Also, please, next time do put this examples(DOLO, DOLOREM, DOLOREUM...) in the question so it is more easy to search for the answer... – VBoka Jan 08 '20 at 17:26
  • Your answer is what I am looking for more or less, but I want to know if it is necessary to put the full text for example "Dolorem" but just by entering "Dolo" that these rows no longer take into account – FeRcHo Jan 08 '20 at 17:26
  • Well your answer seems to be what I am looking for and I will select it, however it would be good to explain a little more what this part does `= 0;` to complement the answer. thanks – FeRcHo Jan 08 '20 at 17:34
  • Another thing you notice is that this performs a full scan of the table, what is the reason? – FeRcHo Jan 08 '20 at 17:37
  • @FeRcHo I have shared the link where the way of the calculation is explained and with that "= 0" is explained... You need to read and research a bit, not all will be served here...I have answered your question(that is very short and not so clear and without examples and so on...) Also, if you have a performance issue this is a new question... – VBoka Jan 08 '20 at 17:41