|
|
Posted by Erland Sommarskog on 05/03/06 00:42
(jim_geissman@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|