How to NOT match on an exception list

• SQLWannabe

  SSCrazy

  Points: 2842

  More actions

  April 8, 2013 at 12:10 pm

  #272790

  If I have a table of Products that have a ProductName and I have a table of ProductNames that I don't want the first table matched to, what is the best process in which to do this? The names will be LIKE each other, but will not match exactly. When I pull data, I only want the ones from dbo.Products where they don't look like the Products from dbo.Exceptions. In my example, I want to pull back Products that don't look like WidgetD or WidgetE.

  Normally I would write:

  SELECT ProductDesc FROM dbo.Products WHERE ProductDesc NOT LIKE '%WidgetD%' AND ProductDesc NOT LIKE '%WidgetE%'

  The query should return WidgetA, WidgetB, and WidgetC (3 records)

  However, I don't want to jack with the code every time a new exception is added.

  Is there a better method? (example code below)

  CREATE TABLE [dbo].[Products](

  [ProductDesc] [varchar](200) NULL

  )

  INSERT INTO Products(ProductDesc) SELECT 'WidgetA'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetB'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetC'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetD'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetE'

  CREATE TABLE [dbo].[Exceptions](

  [ProductDesc] [varchar](200) NULL

  )

  INSERT INTO Exceptions(ProductDesc) SELECT 'Big WidgetD'

  INSERT INTO Exceptions(ProductDesc) SELECT 'Small WidgetE'

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 8, 2013 at 12:24 pm

  #1604527

  fantastic job providing the DDL and sample data!

  I think in this case, you need to match partial strings, right?

  you will want to join the tables together, but use a LIKE statement for the join criteria,(where you might be used to A.ID = B.ID

  this seems to do what i think we are after:

  SELECT Products.*

  FROM Products

  LEFT OUTER JOIN Exceptions

  ON Exceptions.ProductDesc LIKE '%' + Products.ProductDesc + '%'

  WHERE Exceptions.ProductDesc IS NULL

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 8, 2013 at 12:35 pm

  #1604532

  Something like this:

  CREATE TABLE [dbo].[Products](

  [ProductDesc] [varchar](200) NULL

  )

  INSERT INTO Products(ProductDesc) SELECT 'WidgetA'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetB'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetC'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetD'

  INSERT INTO Products(ProductDesc) SELECT 'WidgetE'

  CREATE TABLE [dbo].[Exceptions](

  [ProductDesc] [varchar](200) NULL

  )

  INSERT INTO Exceptions(ProductDesc) SELECT 'Big WidgetD'

  INSERT INTO Exceptions(ProductDesc) SELECT 'Small WidgetE'

  GO

  select

  *

  from

  [dbo].[Products] p

  where

  not exists(select 1 from [dbo].[Exceptions] e where e.ProductDesc like '%' + p.ProductDesc + '%');

  GO

  drop table [dbo].[Products];

  drop table [dbo].[Exceptions];

  GO