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?

Imbecilic

Answer 1

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.

Ode

cool, it works. i'll consider full index search later. right now it is a prototype. it seems we cannot specify word boundaries with instr(). right? full text search in natural language was giving partial matches also, which would need to be fixed. – Imbecilic

Yes, InStr() simply searches for a substring. – Ode

Answer 2

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

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

Ginelle

can you tell me an example for your content – Ginelle

#2124238 – Imbecilic

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

Erastatus

this would match if the column_name string occurs as a substring of a word. it should match on word boundaries only. – Imbecilic

No, it would not. It will match only if the query is surrounded by spaces/start/end. It won't match it when it's followed by a period though. – Ode

it is matching mystringxyz which is what i don't want. i tested it. – Imbecilic

Did you paste the exact query with all spaces? Could you please try which one matches by trying the conditions separately? – Erastatus

why would index searches not work. i could not get your point yesterday also. – Imbecilic

@iamrohitbanga: You are right, actually. The matches should be concat('% ', col) (at end), concat(col, ' %') (at start) and concat('% ', col, ' %') (middle). – Ode

@Max S.: Thanks, don't know what I was thinking :) Corrected now. – Erastatus