SQL Query with Not Like In

SQL Query with NOT LIKE IN

You cannot combine like and in. The statement below would do the job though:

Select * from Table1 
where EmpPU NOT Like '%CSE%' 
AND EmpPU NOT Like '%ECE%' 
AND EmpPU NOT Like '%EEE%'

How to use LIKE and NOT LIKE together in a SQL Server query

All of the strings have either an L or P, which is what %[LP]% looks for.

One way is to escape the pattern:

SELECT TOP (15) * 
FROM [Users] 
WHERE [codename] LIKE '%Luis%' AND
      [codename] NOT LIKE '%/[LP/]%' escape '/';

How do I add multiple NOT LIKE '%?%' in the WHERE clause of sqlite3?

If you use Sqlite's REGEXP support ( see the answer at Problem with regexp python and sqlite for how to do that ) , then you can do it easily in one clause:

SELECT word FROM table WHERE word NOT REGEXP '[abc]';

Not like statement in SQL not working

(L._Stage NOT LIKE '%Not Interested%' OR L._Stage NOT LIKE '%Duplicate%')

This is likely to be always true, unless your have a _stage of 'Not Interested, Duplicate'. When you invert the LIKE you also have to invert your AND/OR logic, so your logic is the opposite of:

(L._Stage LIKE '%Not Interested%' AND L._Stage LIKE '%Duplicate%')

You have the same issue with your start terms. OR combined with NOT will match unless all three of your start terms are in the same column, based on the column name I expect it to only be one, a sort of start date, meaning it will match everything.

So what you really want is probably:

WHEN ((L.Age_Change < DATEADD(day,-180, GETDATE())) 
OR (L.Age_Change IS NULL 
  AND L._Stage IS NOT NULL)
AND (L._Stage NOT LIKE '%Not Interested%'
  AND L._Stage NOT LIKE '%Duplicate%')
AND (L.Start_Term NOT LIKE '%September 2019%'
  AND L.Start_Term NOT LIKE '%September 2020%' 
  AND L.Start_Term NOT LIKE '%September 2021%'))
THEN '1' 

There is likely even more issues with your bracketing, including a lot of redundant parentheses, depending on where you want that OR for l.AgeChange to apply, you want either:

WHEN (L.Age_Change < DATEADD(day,-180, GETDATE() 
  OR L.Age_Change IS NULL) 
AND L._Stage IS NOT NULL
AND L._Stage NOT LIKE '%Not Interested%'
AND L._Stage NOT LIKE '%Duplicate%'
AND L.Start_Term NOT LIKE '%September 2019%'
AND L.Start_Term NOT LIKE '%September 2020%' 
AND L.Start_Term NOT LIKE '%September 2021%'
THEN '1'

To require the age change to be over 180 days or null and all other conditions still matter or:

WHEN (L.Age_Change < DATEADD(day,-180, GETDATE()) 
OR (L.Age_Change IS NULL 
  AND L._Stage IS NOT NULL
  AND L._Stage NOT LIKE '%Not Interested%'
  AND L._Stage NOT LIKE '%Duplicate%'
  AND L.Start_Term NOT LIKE '%September 2019%'
  AND L.Start_Term NOT LIKE '%September 2020%' 
  AND L.Start_Term NOT LIKE '%September 2021%')
THEN '1' 

If age change is >180 days always 1, otherwise if the age change is null check the other conditions.

NOT LIKE IN statement in SQL

You may want to try avoid using REGEXP from performance reasons in case of large data sets.

In such case is TRANSLATE your friend.

1) translate all vowels to one representative

2) perform normal LIKE predicate with the selected vowel

select txt from tab1
where translate(lower(txt),'aeiou','aaaaa') not like 'a%a';

REGEXPs are mighty, but should not be used on non-trivial data sets in case that they could be avoided. (My 8M rows test data gives 7 seconds elapsed using TRANSLATE vs. 2+ minutes with REGEXP).

NOT LIKE and LIKE not returning opposite result

Does this return the correct result ?

Select * from tbl1 WHERE COALESCE([TextCol],'-1') NOT LIKE '%TAX%'

I believe NULL values are the issue here, if the column contains them, then NULL NOT LIKE '%TAX%' will return UNKNOWN/NULL and therefore won't be selected.

I advise you to read about handling with NULL values , or here.

As @ughai suggested, if performance is an issue you can also use:

  Select * from tbl1 
  WHERE [TextCol] NOT LIKE '%TAX%'
     OR [TextCol] IS NULL

Using NOT LIKE in Oracle

SQL Server supports a small subset of regular expression in LIKE pattern. Oracle does not, but it has comprehensive regex support with the regexp_* functions.

Here, you can use regexp_like():

SELECT DISTINCT CITY FROM STATION
WHERE NOT REGEXP_LIKE(CITY, '^[aeiou]', 'i')
ORDER BY CITY ASC;

Regex ^[aeiou] means: one of the listed characters at the beginning of the string (which '^' stands for). The third argiment is called the match parameter: 'i' makes the search case insensitive.

---

Related Topics