Search string by exact word in Mysql

Question

I have a system that searches for company. I want that when a user searches for "Demo", all records that have "Demo" will be returned, like "The Demo", "Demo Inc.", etc. I don't want those records like "Democratic", "Demolition", etc. I think you get my point.

Right now, my working query looks something like this:

select * from table where company LIKE "Demo%"

But that really doesn't hit my requirement. I also tried this one:

select * from table where company RLIKE "[[:<:]]demo[[:>:]]"

The only problem on that one is that it eliminates the possibility of index on my company field. So it searches really slow. I have over a million records right now. Any idea how to do it? If it can't be done in mysql, any idea if it's possible in PHP? Thanks!

Answer 1

Create an Full text index, and then you can search more easy.

ALTER TABLE table ADD FULLTEXT INDEX fulltext_index;


SELECT * FROM table WHERE MATCH (company) AGAINST ('+Demo' IN BOOLEAN MODE); 

dev.mysql.com/doc/refman/5.6/en/fulltext-search.html

Answer 2

You can use REGEXP and the [[:<:]] and [[:>:]] word boundary markers:

SELECT
    *
FROM
    `table`
WHERE
    company REGEXP '[[:<:]]Demo[[:>:]]';

Another Solution

SELECT
    *
FROM
    `table`
WHERE
    company REGEXP '(^|[[:space:]])Demo([[:space:]]|$)';

SQL Fiddle Demo