mysql getting maximum match in like operation

Question

i am executing a sql query which doesn't gives me required output:

Table:

  -----------------------   
  |  col1                |
  ----------------------   
  |  data for hello user |
  |   data               |
   -----------------------

sql query Select * from table where col1 like'%data%' or col1 like '%for%' or col1 like '%hello%' ;

it shows me out put:

  -----------------------   
  |  col1                |
  ----------------------   
  |  data                |
  |  data for hello user |
   -----------------------

but as data for hello user has maximun match with sql like operation so data for hello user comes first and data second

Please help me

Thank you.

You should be using full text search for this. – Abhik Chakraborty Jul 07 '14 at 07:43 — Abhik Chakraborty, Jul 07 '14 at 07:43
do you have any key columns in your table? – Sid M Jul 07 '14 at 07:44 — Sid M, Jul 07 '14 at 07:44

score 1 · Accepted Answer · answered Jul 07 '14 at 07:43

1

Search in internet for fulltext search of mysql. You can obtain what you are searching for.

Example

CREATE TABLE tableName ( 
      tablekey INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      col1 VARCHAR(200), 
      FULLTEXT (col1) 
);


SELECT col1, MATCH(col1) AGAINST('data for help') AS accuracy
FROM table 
WHERE MATCH(col1) AGAINST('data for help') 
ORDER BY accuracy DESC

Will return something like

-------------------------------------
|  col1                |  accuracy  |
-------------------------------------
|  data for hello user |    70      |
|  data                |    10      |
-------------------------------------

answered Jul 07 '14 at 07:43

user3691431

775
6
7

+1 for showing the correct way of doing it. – Abhik Chakraborty Jul 07 '14 at 07:44
error #1214 - The used table type doesn't support FULLTEXT indexes – user3808438 Jul 07 '14 at 08:31
@user3808438 Fulltest search index is supported only for MYISAM or InnoDB tables http://stackoverflow.com/questions/963534/mysql-fulltext-indexes-issue – user3691431 Jul 07 '14 at 09:17
i used `ENGINE=MyISAM DEFAULT CHARSET=latin1; ` There is no problem table has been created but when executing query: `SELECT col1, MATCH(col1) AGAINST('data for help') AS accuracy FROM table WHERE MATCH(col1) AGAINST('data for help') ORDER BY accuracy DESC` output: `MySQL returned an empty result set (i.e. zero rows). (Query took 0.0007 sec)` – user3808438 Jul 07 '14 at 09:34
@user3808438 try removing the where "WHERE MATCH(col1) AGAINST('data for help')". Now you should see the type of data returned by "accuracy"... After that you can set something like "WHERE MATCH(col1) AGAINST('data for help') HAVING accuracy> 0.2" – user3691431 Jul 07 '14 at 09:40
i want to know that is this not working for non-dictionary words – user3808438 Jul 07 '14 at 10:32
`SELECT col1, MATCH(col1) AGAINST('data for help') AS accuracy FROM tablename ORDER BY accuracy DESC;` Output `accuracy=0 accuracy=0` – user3808438 Jul 07 '14 at 10:44
also for data `SELECT col1, MATCH(col1) AGAINST('data') AS accuracy FROM tablename ORDER BY accuracy DESC;` Output `accuracy=0 accuracy=0` – user3808438 Jul 07 '14 at 10:44
@user3808438 table contains data? :D – user3691431 Jul 07 '14 at 11:19

mysql getting maximum match in like operation

1 Answers1