3

Is there a way to configure fuzzy searches in sql server full text search. Meaning if I search for a term called POWDER, I must get matches (i.e. strings) which contain any variations of it within an allowable distance, like for e.g. the matches can be strings which can contain the following variations of the previously mentioned word:

PWDR (distance = 2)
PWDER (distance = 1)
WDER (distance = 2)

I remember reading somewhere you can do this kind of thing, however this specifically requires you to specify the allowable edit distance. How or where do you do this?

Improve this question
3
  • There's no specific implementation of the classical Fuzzy Search (with distance) in 2008 R2 FTS. However, there is one in SQL 2012, it's using the NEAR operator. See this question (and its answers) for some guidelines of what's available. Some options would be: SSIS Fuzzy lookup operator, [Fuzzy logic in Master Data Services], use a T-SQL algorithm.
    – Marian
    Commented Apr 6, 2013 at 12:50
  • An example of how you'd be able to use SSIS for fuzzy match on SQLServerCentral. And for Master Data Services fuzzy functions here.
    – Marian
    Commented Apr 6, 2013 at 12:51
  • 1
    The NEAR operator does NOT allow for fuzzy matching of strings. It allows to express something like "word X must be at most 5 words away from word Y".
    – Andreas
    Commented Jan 27, 2014 at 8:17

2 Answers 2

Reset to default
2

You could try an implementation of one of the string distance functions. Here's a T-SQL implementation of the Levenshtein distance taken from here. That post does suggest there's a performance issue with the function and scalar functions generally can be bad for performance when used in queries, but worth a look. You could always try and convert it to a table function.

enter image description here

Improve this answer
0

This is not possible with current editions of sql server. You may have more luck using some external indexing tool like Lucene.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.