Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

FULLTEXT exact matching problem with a comma separating the words

Posted on 2011-09-29
8
Medium Priority
?
378 Views
Last Modified: 2012-08-13
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
Comment
Question by:dolythgoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 8

Expert Comment

by:PranjalShah
ID: 36818845
Try creating a full text index on your table with that column

ALTER TABLE table_name ADD FULLTEXT(columnName)

 
0
 

Author Comment

by:dolythgoe
ID: 36818850
That has been done - I don't think you can run the query without one!
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 36818949
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:dolythgoe
ID: 36818954
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
 

Author Comment

by:dolythgoe
ID: 36818964
Ah thanks - soz, saw your post after mine! Will check it out and be back...
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36818967
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
 

Author Comment

by:dolythgoe
ID: 36819017
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36819031
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question