1

I have a table called BIO with some biological description that I want to query for keywords in mysql. For example I want to query for "LTR", so I used the code:

select * from BIO where info LIKE '%LTR%';

The result was:

id  info
1   Adenylate isopentenyltransferase       
2   Glycosyltransferase         
3   LTR element
4   Non-LTR retroelement
5   Putative non-LTR 
6   Histone LTR element

I also used as suggested in another question (How to implement a Keyword Search in MySQL?) to avoid some issues:

SELECT * FROM BIO WHERE info LIKE CONCAT('%','LTR','%');

And the result was the same.

I do NOT want a result like in the first 2 lines, for example: Glycosyltransferase

I would like to have:

id  info
3   LTR element
4   Non-LTR retroelement
5   Putative non-LTR 
6   Histone LTR element

How is the best way to do it?

Community
  • 1
  • 1
Alex
  • 355
  • 1
  • 7
  • 20

1 Answers1

1

This is a bit tricky. But for your examples, the following will work:

select *
from BIO
where concat(' ', replace(info, '-', ' '), ' ') LIKE '% LTR %';

What is the logic? It places a space at the beginning and end of info and replaces all hyphens with spaces. For the examples in your question, the occurrence of LTR that you want would then be " LTR ".

Note: You could also work on a regular expression to do the same thing. But it is tricky with the word boundaries and the matches at the beginning and end of the string.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Great suggestion. Is it possible to add to this query also something to get also "Histone/LTR" or "LTR/anything"? I forgot to include this in my question. – Alex Mar 15 '17 at 21:36
  • 1
    It worked with this one - select * from BIO where concat(' ', REPLACE(REPLACE(info, '-', ' ') , '/', ' '), ' ') LIKE '% LTR %'; – Alex Mar 15 '17 at 22:01