mysql: instr specify word boundaries

Question

i want to check if a string contains a field value as a substring or not.

select * from mytable where instr("mystring", column_name);

but this does not search on word boundaries.

select * from mytable where instr("mystring", concat('[[:<:]]',column_name,'[[:>:]]');

does not work either. how to correct this?

Answer 1

If you don't need the return value of the instr use like instead

select * from mytable where column_name like '%mystring%';

Answer 2

You can do this using the REGEXP operator:

SELECT * FROM mytable WHERE 'mystring' REGEXP CONCAT('[[:<:]]', column_name, '[[:>:]]');

Note, however, that this is slow. You might be best off using the MySQL's FULLTEXT search feature if you care about words. Or do a normal InStr() check then filter the results.

Answer 3

As already discussed in the question you asked yesterday, no indexes can be used and performance is going to be bad, but this could work:

select *
from mytable
where 'mystring' = column_name                           -- exact match
   or 'mystring' like concat('% ', column_name)          -- word at the end
   or 'mystring' like concat(column_name, ' %')          -- word at beginning
   or 'mystring' like concat('% ', column_name, ' %')    -- word in the middle