SQL question - how to find the 'nearest match', a sort of 'FUZZY LIKE' select?
This is a wierd sort of question I know but I can't find any other way around the problem at the moment. I'm writing some code (in PHP, in a Dokuwiki plugin) which retrieves data from a database when a link is clicked. The link one clicks is a town name but, because it's a link, Dokuwiki massages the name to make it not have any 'difficult' characters in it - i.e. it switches to all lower case, accented characters are un-accented and non-letters are changed to underscores. E.g. :- Deûlémont becomes deulemont Halluin/Menin becomes halluin_menin In my plugin I get the massaged town names and I want a way to run a select on the database to get the data for those towns. Can anyone suggest a way that one might be able to do this? I guess I can change all underscores to wildcards, that gets over that issue, but I can't see how to handle the accented characters, i.e. :- deulemont won't match Deûlémont even using LIKE I'm using the default Sqlite3 at the moment, does anyone know if any of the more 'heavyweight' databases have a way to fuzzy match accented characters with their non-accented equivalents? I can quite easily switch to mySql or postgresql. -- Chris Green
On Thu, Dec 16, 2010 at 11:50:25AM +0000, Chris G wrote:
This is a wierd sort of question I know but I can't find any other way around the problem at the moment.
I'm writing some code (in PHP, in a Dokuwiki plugin) which retrieves data from a database when a link is clicked. The link one clicks is a town name but, because it's a link, Dokuwiki massages the name to make it not have any 'difficult' characters in it - i.e. it switches to all lower case, accented characters are un-accented and non-letters are changed to underscores. E.g. :-
Deûlémont becomes deulemont Halluin/Menin becomes halluin_menin
In my plugin I get the massaged town names and I want a way to run a select on the database to get the data for those towns. Can anyone suggest a way that one might be able to do this?
I guess I can change all underscores to wildcards, that gets over that issue, but I can't see how to handle the accented characters, i.e. :-
deulemont won't match Deûlémont even using LIKE
I'm using the default Sqlite3 at the moment, does anyone know if any of the more 'heavyweight' databases have a way to fuzzy match accented characters with their non-accented equivalents? I can quite easily switch to mySql or postgresql.
Hmmm, it appears that mySql will simply match the way I want with no effort at all:- SELECT * from towns where name = 'deulemont' will find Deûlémont. I'm surprised but it solves my problem! -- Chris Green
participants (1)
-
Chris G