Search code examples
mysqlregexword-boundary

mysql: instr specify word boundaries

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?

Solution

  • 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.