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.