• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

FULLTEXT exact matching problem with a comma separating the words

Hi all,

So I have a simple FULLTEXT query that performs an exact match:

SELECT link_id FROM textdump
WHERE MATCH (link_tags)
AGAINST ('"one two"' IN BOOLEAN MODE)

Open in new window


Problem is, this sees "one,two" as an exact match when it isn't as there's a comma between them.

How can I get round this?

Cheers
David
0
dolythgoe
Asked:
dolythgoe
  • 4
  • 3
1 Solution
 
PranjalShahCommented:
Try creating a full text index on your table with that column

ALTER TABLE table_name ADD FULLTEXT(columnName)

 
0
 
dolythgoeAuthor Commented:
That has been done - I don't think you can run the query without one!
0
 
Kevin CrossChief Technology OfficerCommented:
The problem is that punctuation is not treated as stop words, but I do not have a good answer how to fix that; however, you can see if this blog is helpful: http://thenoyes.com/littlenoise/?p=91
I believe the author is an EE member, but I have not seen him posting in a bit.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
dolythgoeAuthor Commented:
Found this on MySQL site:

Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

Hmm not helpful in my case! There must be way to match on the ',' as words are often delimited by them..
0
 
dolythgoeAuthor Commented:
Ah thanks - soz, saw your post after mine! Will check it out and be back...
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, exactly. See if link above helps as it shows how to make some punctuation stop words. I am emailing who I believe the author is to see if he will post here, since I have not tried this out myself. If it works for you, then great. If not, then hopefully someone like that can confirm; however, per the documentation it is working as designed by MySQL as you have found.
0
 
dolythgoeAuthor Commented:
Uhhh, that's a bit of a ball ache :( ! I might just match anyway and then get php to check if there's a comma in it or not - it's only for a worker-type cron script so I might skip the character encoding bit.

Will award points - it's the right solution! Thanks.
0
 
Kevin CrossChief Technology OfficerCommented:
Cool. Keep in mind too, you might be able to do this outside of FULLTEXT.

SELECT link_id
FROM textdump
WHERE link_tags LIKE '%one two%'

Kevin
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now