How to measure how exactly a column matches a pattern?

views:

answers:

+1 Q:

How to measure how exactly a column matches a pattern?

I'd like to have the results of my full text search in MySQL sorted by how completely the pattern covers the match.

For example searching for apple in a nutrition database should sort "apple, raw" higher than "apple fritter" since 5/9 > 5/12.

I can do this rather trivially outside the database, but i'm looking for a query that'll do it in one shot.

Any ideas?

Thank you.

+2 A:

ORDER BY char_length(someColumn)/char_length(replace(someColumn, 'apple', ''))

longneck 2009-09-23 18:08:17

Possible bug, when someColumn contains just 'apple'. consider reversing the fraction and sorting DESC

mjv 2009-09-23 18:17:31

Performance improvement tip: use ... char_length(somecolumn) - char_length('apple') rather than the replace() construct. Assuming that 'apple' will be found somewhere in some_column (implicit to the search criteria applied in where clause) this allows to avoid all the computation of replace()

mjv 2009-09-23 18:18:43