How to use LIKE and IN combined???

• tzanouch

  SSC Rookie

  Points: 34

  More actions

  June 22, 2011 at 2:46 am

  #253234

  Hi everybody,

  I would like to write something like:

  ...

  where Temp1 not like %abc% and not like and not like %dae% ... etc.

  Is there any way to combine the LIKE and the IN as the title says?

  Thank you!

  Teo

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 22, 2011 at 8:38 am

  #1342441

  You mean something like this?

  ;WITH TableRows AS (

  -- Some sample data, let's assume

  -- that it comes from your table

  SELECT *

  FROM (

  VALUES ('Some Value Here'),

  ('abc'),

  ('Another Row'),

  ('Film')

  ) AS TableRows(value)

  ),

  Patterns AS (

  -- The patterns to match

  SELECT *

  FROM (

  VALUES ('abc'),

  ('dae'),

  ('fgh'),

  ('ilm')

  ) AS Patterns(p)

  )

  SELECT *

  FROM TableRows

  WHERE NOT EXISTS (

  SELECT 1

  FROM Patterns

  WHERE value LIKE '%' + p + '%'

  )

  Edit: code formatting and comments

  -- Gianluca Sartori