Search code examples
sql-serversql-server-2008t-sqlsql-delete

SQL like % delete records?

I have delete query that should remove all records that are not matching 01 or 10. These two numbers are in front of the word separated with dash. That looks like this:

Column_1
01-Test1
01-Test2
10-Test3
10-Test4

My query should delete all records that are not matching 01 and 10 in front of the word. Here is my query:

DELETE FROM MyRecords
WHERE Column_1 NOT LIKE '01%' OR Column_1 NOT LIKE '10%'

For some reason query above deletes all records from the list above. I'm not sure where I missed something that removes all records. If anyone see the problem please let me know. Thank you.

Solution

  • Change your OR to AND

    DELETE FROM MyRecords
WHERE Column_1 NOT LIKE '01%' AND Column_1 NOT LIKE '10%'

    Another option

    DELETE FROM MyRecords
WHERE Left(Column_1,2)  NOT IN ('01','10')

    (A little more readable but won't use the index)