0

I have a MySQL table with a list of sentences such as:

id | Sentence 
---+---------------------------------------------------------------
 1 | Come On Boys, You Can do it my boy.
 2 | Everything is possible, impossible itself says I am possible.
 3 | Boys I know possible solutions are good!
 4 | Possible solutions are all failed its also possible

I have to search on Sentence column exact word like possible.

There are some condition :

  1. Searching word can't be prefix or suffix of any word.

    • Searching word possible , impossible is incorrect.

  2. Punctuation mark can be before or after the word.Ex: !boy or boy,

  3. No case sensitive means for possible Possible is correct.

I try a query but it is not working

mysql_query("SELECT * FROM products WHERE Sentence LIKE '%".$search."%'");

Possible punctuation mark are ,.!?;-

I need a working solution. Thanks in advance !

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Eklavya
  • 17,618
  • 4
  • 28
  • 57
  • You can use a regexp. – Funk Forty Niner Mar 31 '17 at 12:40
  • fulltext index and search is what you are looking for. – Shadow Mar 31 '17 at 12:40
  • Shadow: No, see condition (ii) and (iii). – Eklavya Mar 31 '17 at 12:46
  • Words indexed by the fulltext index will not include punctuation marks. iii depends on the collation used, but by default the search is done in a case insensitive mode. – Shadow Mar 31 '17 at 12:47
  • @Fred-ii- pls check out the duplicate link and wield your hammer if you think it is appropriate – Shadow Mar 31 '17 at 12:56
  • @Shadow Seeing [this comment by the OP...](http://stackoverflow.com/questions/43140920/mysql-how-to-search-for-exact-word-match-in-a-colomn-of-sentence?noredirect=1#comment73359043_43140992) in an answer, uses the same code (somewhat) from the possible dupe. The OP really should have spend more time researching this and trying different methods, IMO anyway. But.. .that dupe may be it. *Thinking............* – Funk Forty Niner Mar 31 '17 at 12:58
  • @Fred-ii- the OP also said in a comment above that fulltext index would not satisfy conditions ii and iii, which clearly indicate that he did not properly check out fulltext indexes (or did not describe the criteria completely). – Shadow Mar 31 '17 at 13:04
  • I really don't know 'fulltext indexes' what is this means ? – Eklavya Mar 31 '17 at 13:09
  • And I see this solution http://stackoverflow.com/questions/656951/search-for-whole-word-match-in-mysql but this solution won't satisfy my condition I think that . – Eklavya Mar 31 '17 at 13:14
  • @Fred-ii- since the duplicate topic has the same answer (http://stackoverflow.com/a/42038254/5389997) as Tim's, you can safely mark it as a duplicate. Or use the link in Rafaels answer below. Same difference. – Shadow Mar 31 '17 at 22:00

3 Answers3

2

You could use REGEXP:

SELECT *
FROM products
WHERE Sentence REGEXP '[[:<:]]possible[[:>:]]'
--                       ^^^            ^^^ word boundaries

This would correspond to matching possible surrounded by a word boundary. Note in the demo that punctuation counts as a word boundary.

Also note that MySQL's REGEXP is case insensitive, so the above should match possible or Possible.

Demo here:

Rextester

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
2

Use REGEXP:

SELECT *
FROM products
WHERE Sentence REGEXP '[^\\d\\w]possible[^\\d\\w]'

This takes every word possible surrounded by any word or number.

Be carefull with case sensitivity.

Po1nt
  • 522
  • 1
  • 4
  • 13
1
CREATE TEMPORARY TABLE tmp (id INTEGER PRIMARY KEY AUTO_INCREMENT, sentence VARCHAR(200));

INSERT INTO tmp (sentence) VALUES ('Come On Boys, You Can do it my boy.');
INSERT INTO tmp (sentence) VALUES ('Everything is possible, impossible itself says I am possible.');
INSERT INTO tmp (sentence) VALUES ('Boys I know possible solutions are good!');
INSERT INTO tmp (sentence) VALUES ('Possible solutions are all failed its also possible');

SELECT * FROM tmp WHERE sentence REGEXP '[[:<:]]impossible[[:>:]]';

This is duplicate: Search for "whole word match" in MySQL

Community
  • 1
  • 1
Rafael Fialho
  • 31
  • 5