SQL Exclude LIKE items from table

Question

I'm trying to figure out how to exclude items from a select statement from table A using an exclusion list from table B. The catch is that I'm excluding based on the prefix of a field.

So a field value maybe "FORD Muffler" and to exclude it from a basic query I would do:

SELECT FieldName 
FROM TableName 
WHERE UPPER(ColumnName) NOT LIKE 'FORD%'

But to use a list of values to exclude from a different tabel I would use a Subquery like:

SELECT FieldName 
FROM TableName 
WHERE UPPER(ColumnName) NOT IN (Select FieldName2 FROM TableName2)

The problem is that it only excludes exact matches and not LIKE or Wildcards (%).

How can I accomplish this task? Redesigning the table isn't an option as it is an existing table in use.

EDIT: Sorry I am using SQL Server (2005).

Answer 1

I think this will do it:

SELECT FieldName
FROM TableName
LEFT JOIN TableName2 ON UPPER(ColumnName) LIKE TableName2.FieldName2 + '%'
WHERE TableName2.FieldName2 IS NULL