Multiple NOT LIKE in sql server

user_8669616 0
Viewed 64

I have a table like

+--------+-------+
| id     | name  |
+--------+-------+
| 302345 | Name1 |
| 522345 | Name2 |
| 1X2345 | Name3 |
| 2X2345 | Name4 |
| 1X8765 | Name5 |
| 2X2123 | Name6 |
| 502345 | Name7 |
| M62345 | Name8 |
+--------+-------+

I want to take id that doesn't have prefix 30,1X,2X. Like this I have more than 20 prefix to be excluded. I was using NOT LIKE 20 times and looking to shorten it. From some of stackoverflow question, I have found we can create a table and store all these values in a column and use that. But in my case I don't have permission to create table. Hence tried the below code but it is giving strange results.

SELECT *
FROM mytable
INNER JOIN (
    SELECT '30%' Prefix
    UNION ALL
    SELECT '50%'
    UNION ALL
    SELECT '1X%'
    ) list ON id NOT LIKE prefix

FIDDLE HERE . Please suggest some alternative.

Expected output

+--------+-------+
| id     | name  |
+--------+-------+
| 522345 | Name2 |
+--------+-------+
| 502345 | Name7 |
+--------+-------+
| M62345 | Name8 |
+--------+-------+
sql-server-2012 sql-server
1 Answers

You could use a NOT EXISTS with a VALUES construct for all your prefixes.

Something like this:

SELECT *
FROM mytable mt
WHERE NOT EXISTS (SELECT 1
                  FROM (VALUES('30%'),('50%'),('1X%'),('2X%')/*,...*/)V(expr)
                  WHERE mt.id LIKE V.expr);
user_20299830
Related