Posted by jim_geissman on 10/02/72 11:47

An application filters records based on names found in them. For
example, looking through property buyer names, looking for banks and
relocation companies.

I have a table of names and patterns:

CREATE TABLE #Filters (Pattern varchar(100), IfWildCard int, Category
int)
INSERT #Filters SELECT 'Bank Of America', 0, 1
INSERT #Filters SELECT '% Bank %', 1, 2
INSERT #Filters SELECT 'Bank %', 1, 2
INSERT #Filters SELECT '% Bank', 1, 2
INSERT #Filters SELECT 'Credant Reloc%', 1, 3
INSERT #Filters SELECT '%Relocation%, 1, 3

The filtering matches the table of candidate names against the filters,
and returns Category, where the where clause or join is
(Candidate = Pattern AND IfWildCard = 0)
OR (Candidate LIKE Pattern AND IfWildCard = 1)

"Bank of America" matches an exact pattern, and also a wildcard
pattern, and the two different matches give different values for
Category. Is there a way to control which match takes precedence, or
is necessary to do it multiple times in the desired order, removing
those that hae already matched from consideration?

Thanks,
Jim Geissman

[Back to original message]