searching words in full text with apostrophes

[rondog]

Say I have one row with the word "ronnie" and another row with the word "ronnie's"

 

When I do a match() against() with the keyword being "ronnie", the row containing the word "ronnie's" isn't included as a result. How can I overcome that?

[bh]

Hi,

 

try to use the wildcard operator: 'ronnie*'. But if your searched text sg like this '"some words"', it will search exactly the words: 'some words'.

 

 

full-text manual

[rondog]

using the wild card works one way..but if I type "ronnie's" it doesn't find a record that contains "ronnies" because there is no apostrophe.

[fenway]

You can't overcome that easily -- the apostrophe is considered a non-word character, so the index stores "ronnie" and "s" -- and throws out "s" because it's too short.  "Ronnies" is a different word entirely.

 

If you want these to be the same, you'll need to strip out apostrophes from the column you're searching -- or, alternatively, store a "cleaned" column value instead, and search this.  This is commonplace, since you often need to strip out HTML, too.