Search code examples
sqlsql-server

Check if a column does not contain a list of strings

Is there a way to check if a column does not contain a list of strings? My goal is to figure out if there are any other type of patterns of string beside apple, banana, or orange in the FileNm column.

This doesn't work because it will still show columns that either have apple, orange or banana... the logic is flawed.

SELECT *
FROM dbo.File f
WHERE (CHARINDEX('apple', f.FileNm ) = 0
OR (CHARINDEX('orange', f.FileNm ) = 0
OR (CHARINDEX('banana', f.FileNm ) = 0)
Solution

  • Change from OR to AND

    SELECT *
FROM dbo.File f 
WHERE (CHARINDEX('apple', f.FileNm ) = 0
    AND (CHARINDEX('orange', f.FileNm ) = 0
    AND (CHARINDEX('banana', f.FileNm ) = 0)

    Could also write it like this

    SELECT *
FROM dbo.File f
WHERE f.FileNm NOT LIKE '%apple%'
    AND f.FileNm NOT LIKE '%orange%'
    AND f.FileNm NOT LIKE '%banana%'