2

I'm trying to use wildcards to pass a stem of a word as part of a full text search in MySql. I would prefer to use match...against for the performance benefit instead of a like query.

I found this post which makes it sound as though this can be done: MySQL fulltext with stems

...but I can't get it to work for me.

My data looks like this:

table name: "rxnorm_brands"
step_medname              bn_name
Amoxicillin               Wymox
Amoxicillin-Clavulanate   Augmentin

This query works but uses "like":

select `step_medname`, `bn_name`
from `rxnorm_brands`
where (`bn_name` like 'Amox%' or `step_medname` like 'Amox%');

I want to use this query, but it returns nothing:

select `step_medname`, `bn_name`
from `rxnorm_brands`
where MATCH (`bn_name`, `step_medname`) AGAINST ('Amox*');

I do have a fulltext index on bn_name and step_medname. What am I doing wrong? Or can this not be done?

Community
  • 1
  • 1
Marvin
  • 569
  • 6
  • 23

2 Answers2

2

This can be done using IN BOOLEAN MODE, see: http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html.

So your query would become:

select `step_medname`, `bn_name`
from `rxnorm_brands`
where MATCH (`bn_name`, `step_medname`) AGAINST ('Amox*' IN BOOLEAN MODE);

but note that with the BOOLEAN MODE matching, rows either match or they don't - the results can no longer be ordered by relevance like they can with normal FULLTEXT searches.

Tim Fountain
  • 33,093
  • 5
  • 41
  • 69
-1

The RxNorm API now has a method that will do matching of text that only approximately matches the RxNorm concept. See http://rxnav.nlm.nih.gov/RxNormAPI.html#label:23

David Mann
  • 1,874
  • 2
  • 16
  • 19