Does anyone know of a good add on that will allow fuzzy text searches?
(sql server 2008)
Specifically, I'm building up a database of how much various items, eg second hand cameras sell for on eBay. I've already written the code to download the data. But to query the database is tricky, because people keep misspelling the names of the cameras. For example, a Canon IXUS 90 IS might be entered as Canon 9015. Or they might write Cannon instead of Canon. I want a search that is intelligent enough to recognise these as near misses.
-- Difference - Change the when comparison strings to test this code snippet
select case when difference('Canon IXUS 90','Cannon 9015') = 0 then 'no matching chars'
when difference('Canon IXUS 90','kannon 9015') = 1 then 'do something <-- 1 matching chars'
when difference('Canon IXUS 90','Cannon 9015') = 2 then 'do something <-- 2 matching chars'
when difference('Canon IXUS 90','Cannon 9015') = 3 then 'do something <-- 3 matching chars'
when difference('Canon IXUS 90','Caknon 9015') = 4 then 'do something <-- best match'
else 'do something else' end
Possibly if the data was text and not a varchar column in a table. Although the op said text, I wonder if op meant column data? Don't know the db table design.
"such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word."
Sounds like they are using soundex or different functions. A bit more complex though.
I have a couple of those laying around somewhere. I also have one that will do a search on every column, table in the db for a specific string. I use it to look for table(s), row, column where you might find 'test'.