views:

19

answers:

0

Hi all,

I'm trying to define a table in a SQL Server database that will hold rules. The rule data will be keyed on a number of columns. Where rules apply to a number of scenarios I want the the key columns to contain wildcards to avoid having to maintain lots of data. I then want to find the best match row with some kind of fuzzy search. Here's an example:

#   Col1  Col2  Col3
============================
1     A     B     C
2     A     B     *
3     A     *     *
4     *     *     C

The say I have a stored proc called GetRuleData(Col1, Col2, Col3) and these are the results I want:

GetRuleData(A, B, C) returns row 1 only
GetRuleData(A, B, A) returns row 2 only
GetRuleData(A, A, A) returns row 3 only
GetRuleData(C, C, C) returns row 4 only
GetRuleData(B, B, B) returns no rows

I would appreciate any ideas on the best way to do the query. I've been experimenting and thinking along the lines of a series of UNIONs but perhaps I'm barking up the wrong tree. The real table is also a bit more complex in that I have a rule number column so multiple rules can be returned.

I'm also open to re-designing this data to make the query easier! :)

Thanks for any pointers, Chris.