String matching precedence

[jim_geissman]

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

[Stu]

Well, how would YOU determine precedence? Is an exact match better
than a wildcard match? Without more details, its difficult to give
advice.

Stu

[jim_geissman]

I know the order of preference. Let's say that exact matches take
preference over wildcards, or alternatively they should be in ascending
order by the Category column.

My question is, given that I know it, how to ensure the matching is
done in that order, as opposed to randomly or according to the whims of
SQLS. I'm thinking that multiple passes may be required, but I would
like to avoid that on the assumption it would be quicker to do it all
at once.

For example, to do them in order by Category, I could outer join the
candidates to (SELECT * FROM #Filters WHERE Category=1) AS f1 ON ...
and also outer join to (same thing, Category=2) AS f2, and so forth.
Would those matches be attempted in the order the joins appear?

Thanks,
Jim

[Erland Sommarskog]

(ji**********@countrywide.com) writes:

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?

SELECT TOP 1 ...
FROM ...
WHERE ...
ORDER BY Category

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jim_geissman]

Thanks, Erland. I have a whole table of Candidates, that may match one
or more filters, and I would like to filter them all at once. Does
your suggestion do that? I'm afraid my SQL is so limited that I don't
see how. I didn't mention this, but I have roughly 1,000 filters and
100 mil names. Of the names, maybe 10 mil match a filter, with maybe
1/4 of those matching more than one.

Thanks,
Jim

[jim_geissman]

Now I think I see it -- select candidate, min(category)
from candidates join filters on ... group by candidate

Sorry to start a wild goose chase.

Thanks,
Jim