If WPP.COMMENT contains NULL, the condition will not match.
This query:
SELECT 1
WHERE NULL NOT LIKE '%test%'
will return nothing.
On a NULL column, both LIKE and NOT LIKE against any search string will return NULL.
Could you please post relevant values of a row which in your opinion should be returned but it isn't?
Answer from Quassnoi on Stack OverflowIf WPP.COMMENT contains NULL, the condition will not match.
This query:
SELECT 1
WHERE NULL NOT LIKE '%test%'
will return nothing.
On a NULL column, both LIKE and NOT LIKE against any search string will return NULL.
Could you please post relevant values of a row which in your opinion should be returned but it isn't?
Just come across this, the answer is simple, use ISNULL. SQL won't return rows if the field you are testing has no value (in some of the records) when doing a text comparison search, eg:
WHERE wpp.comment NOT LIKE '%CORE%'
So, you have temporarily substitute a value in the null (empty) records by using the ISNULL command, eg
WHERE (ISNULL(wpp.comment,'')) NOT LIKE '%CORE%'
This will then show all your records that have nulls and omit any that have your matching criteria. If you wanted, you could put something in the commas to help you remember, eg
WHERE (ISNULL(wpp.comment,'some_records_have_no_value')) NOT LIKE '%CORE%'
How to put several LIKE & NOT LIKE statements in WHERE SQL clause?
NOT LIKE Alternatives in WHERE clause – SQLServerCentral Forums
SQL LIKE ANd NOT LIKE - SQLTeam.com Forums
NOT LIKE with '%'
Videos
Use brackets to make it easier to see, and no commas (probably a typo)
WHERE (ColumnName NOT LIKE ‘string’)
AND (ColumnName NOT LIKE ‘string2’)
Guys,
I am doing a SELECT statement and need to combine several LIKE… & NOT LIKE things in my WHERE clause to wash the data.
It will let me do: WHERE ColumnName NOT LIKE ‘string’
But when I try to add several of these it gives me an error.
For ex:
WHERE ColumnName NOT LIKE ‘string’,
AND ColumnName NOT LIKE ‘string2’
What is the proper syntax to combine several LIKE & NOT LIKE parameters in the same WHERE clause of a Microsoft SQL Server SELECT statement?
Thank you!!!
So using MS SQL Server, I am wondering why this works (get cities that start and end with vowel)
SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE '[AEIOU]%[AEIUO]'
but for finding cities that do not start and end with vowel I can't just add a NOT into the above SQL, instead I have to do
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT LIKE '[AEIOU]%' AND CITY NOT LIKE '%[AEIUO]'
Access supports two dialects of SQL: Access SQL and ANSI-92 SQL. The latter is compatible with SQL Server; it uses % as wildcard for any number of characters, and _ for a single character, instead of * and ? respectively.
You can specify the version to use in File > Options > Object Designers > SQL Server Compatible Syntax (ANSI 92) in the Query design section.
The SQL looks correct!
Are you sure that there are customers whose name starts with A?