CODEWITHSUNDEEP
mysqlfull-text-search
Jay
Jay

Reputation: 763

MySQL - Finding partial strings - Full Text?

I just found a bunch of rogue data in my MYSQL db...

The only way to get to it is via one of the columns - FILE_PATH which contains a slash stripped version of a file path. There are a few rogue files in this set that I need find - they all have the file name "Thumbs.db" but they have a variety of paths

example:

F:DatasetGroupedByFormatsx-fmt-398Thumbs.db

I have a full text index on the field, however the following query doesn't give any returns:

SELECT * FROM main_small WHERE MATCH `FILE_PATH` AGAINST  ('Thumbs.db')

Response:

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0382 sec )

I am unsure whether this is because I have the syntax wrong, or whether the text string needs to be isolated by whitespace/punctuation.

Upvotes: 6

Views: 28083

Answers (3)

curtisdf
curtisdf

Reputation: 4210

The problem is that your query thinks 'Thumbs.db' is a whole word. You'll need to find some way to do wildcard searching in order to select those rows. How about:

SELECT * FROM main_small WHERE `FILE_PATH` LIKE '%Thumbs.db'

Upvotes: 3

kitti
kitti

Reputation: 14794

Just use LIKE:

SELECT * FROM main_small WHERE `FILE_PATH` LIKE '%Thumbs.db'

Upvotes: 2

Andrew Leach
Andrew Leach

Reputation: 12973

Surely it's

select * from main_small where FILE_PATH like '%Thumbs.db'

However, if not then does MySql Full text Search help?

Upvotes: 13

Related Questions