How to generate permutations/combinations of search terms for SQL statements.
Posted on 2013-06-28
Hello all. I have a problem that I expect some of you have seen many times. I want to search descriptions in a SQL 2000 table for the best match with a number of search terms. Suppose someone supplies (for example) five words, I'd like to find rows where all five are present, then rows where any four are present, then rows where any three are present. And so on. I'm sure there's a name for this sort of thing, but my own searches haven't found anything that describes exactly what I'm looking for. Have any of you done this before? Since it's SQL 2000, I'm guessing there aren't any nice built-in functions for this. If someone can even describe how to generate the combinations, I can probably come up with the SQL by myself.