More concise way of querying list of Not Likes

Question

I have the following SQL query:

Select X, Y  
FROM Table.dbo 
WHERE DESCRIPTION NOT LIKE 'X' 
    AND DESCRIPTION NOT LIKE 'Y' 
    AND DESCRIPTION NOT LIKE 'Z' 
    AND DESCRIPTION NOT LIKE 'A' 
    AND DESCRIPTION NOT LIKE 'B' 
    AND DESCRIPTION NOT LIKE 'C'  
    AND DESCRIPTION NOT LIKE 'D';

Is there a more efficient way of writing this query?

Ideally DESCRIPTION NOT LIKE 'Y','Z','A','B','C','D' and so on.

Thanks!

Are you using MySQL Or SQL Server? Please pick one or the other. — Aaron Bertrand, Dec 11 '13 at 20:37
@Justin is the answer there going to be the same as the one here? We don't even know whether this OP is using MySQL or SQL Server, but the dupe you've identified is for neither of those, it's for Oracle. — Aaron Bertrand, Dec 11 '13 at 20:38
@JustinPihony - Doubt the syntax will be. The SQL Server pattern syntax supports ranges and sets. MySQL would need regex instead of `LIKE` for that AFAIK. — Martin Smith, Dec 11 '13 at 20:42
Why are you using `LIKE` here anyway? With no wildcards it is the same as `=`? — Martin Smith, Dec 11 '13 at 20:44
This is set logic. Is there a way to define the set you don't want to include without having to define every element in the set? Say for example you have (1a, 1, 1b, 1c, 2d, 3e, 4, 5, 4g) and you want to list only those that are numeric. it would be a simple mater to check if it's numeric then you'd have set to work with and could use not in. or you could use a CTE (common table expression) and if possible define the set there and use an outer join to exclude unwanted records. but this all depends on having an ability to define the set to use w/o having to define every element in the set — xQbert, Dec 11 '13 at 20:50
This is not a duplicate of the Oracle question. The SQL Server pattern syntax is different (supports sets and ranges of characters). — Martin Smith, Feb 28 '14 at 23:09

Martin Smith · Answer 1 · 2013-12-11T20:58:15.553

You can use.

Select X, Y  
FROM YourTable
WHERE DESCRIPTION NOT LIKE '[A-DYZ]'

I guess maybe you meant to include a trailing wildcard in the patterns? In which case

WHERE DESCRIPTION NOT LIKE '[A-DYZ]%'

If the patterns are arbitrarily different you can use

SELECT X,
       Y
FROM   YourTable
WHERE  NOT EXISTS (SELECT *
                   FROM   (VALUES ('%cars%'),
                                  ('%boats%'),
                                  ('%trains%')) V(C)
                   WHERE  DESCRIPTION LIKE V.C)

Or alternatively use CLR integration. Install a regular expression function and search for rows not matching the Regex (cars|boats|trains)

Thanks. So if I wanted to exclude cars, boats, trains, would the logic look like WHERE DESCRIPTION NOT LIKE '[%Cars%,%Boats%,%Trains%]' ? — ZJAY, Dec 11 '13 at 20:54

More concise way of querying list of Not Likes

1 Answers1