like and not like in mysql query

Question

I want to do a query containing 'like' and 'not like'. Current query:

SELECT * 
FROM `final` 
WHERE ( `T_Degree` LIKE '%Keyword1%' ) 
  and (`A_Author_Translator_State` NOT LIKE 'Keyword2' 
        or `T_Author` NOT LIKE 'Keyword2' 
        or `T_Author_State` NOT LIKE 'Keyword2' ) 
ORDER BY `Kind` ASC

But that doesn't work. Any tips?

Could you exactly say what `doesn't work` means in this case? — Math, Dec 10 '13 at 16:31
What error messages are you getting? The more you share, the easier it will be to resolve the issue. — Robbert, Dec 10 '13 at 16:47

score 1 · Answer 1 · answered Dec 10 '13 at 16:50

Just change the or to and, assuming you want none of the three fields to be like 'Keyword2':

SELECT * 
FROM `final` 
WHERE ( `T_Degree` LIKE '%Keyword1%' ) 
  and (`A_Author_Translator_State` NOT LIKE 'Keyword2' 
        and `T_Author` NOT LIKE 'Keyword2' 
        and `T_Author_State` NOT LIKE 'Keyword2' ) 
ORDER BY `Kind` ASC;

By the way, because you are not using wildcards, you can phrase this as:

SELECT * 
FROM final 
WHERE T_Degree LIKE '%Keyword1%' and
      Keyword2 not in (A_Author_Translator_State, T_Author, T_Author_State)
ORDER BY Kind ASC;

score 0 · Answer 2 · answered Dec 10 '13 at 16:34

0

You may need to clarify better what it is you're trying to achieve but this may work for what you want.

SELECT * 
FROM `final` 
WHERE ( `T_Degree` LIKE '%Keyword1%' ) 
  AND NOT (`A_Author_Translator_State` LIKE 'Keyword2' 
        or `T_Author` LIKE 'Keyword2' 
        or `T_Author_State` LIKE 'Keyword2' ) 
ORDER BY `Kind` ASC

answered Dec 10 '13 at 16:34

AndyL

1
1

Hi, I want the records wich has pharas "key1" in their "F1" field be shown with exception of the records wich has pharase "key2" in their "F2", "F3","F4",.... fields. tnx – Tohid Niknami Dec 11 '13 at 06:31

Nic Wortel · Answer 3 · 2013-12-10T17:09:26.850

If you don't use a wildcard (%), you're basically checking if both are the same.

In other words:

WHERE `ColumnName` LIKE 'Value'

is the same as:

WHERE `ColumnName` = 'Value'

(you only find the records where 'Value' and the contents of the column are exactly the same)

If you want to find records where ColumnName contains that value, you'll need to use wildcards:

WHERE `ColumnName` LIKE '%Value%'

If you only want to find records where the value of ColumnName starts with 'Value' (in other words, there shouldn't be anything in front of it), use:

WHERE `ColumnName` LIKE 'Value%'

Example

Let's consider this table (called myTable):

ID | Description
----------------
 1 | Lorem Ipsum dolar sit amet
 2 | Lorem ipsum FooBar dolar sit amet
 3 | FooBar
 4 | Foo Bar

Now this query:

SELECT * 
FROM `myTable`
WHERE `Description` LIKE '%FooBar%'

will return rows #2 and #3. Row #1 will not be returned because it doesn't contain 'FooBar'. Row #4 will not be returned because it doesn't contain 'FooBar', either. (it does contain 'Foo Bar', but that's not the same)

Now let's see what happens using another query:

SELECT * 
FROM `myTable`
WHERE `Description` LIKE 'FooBar%'

(note that the % before FooBar has been removed)

This query will only return row #3, because that's the only row that starts with FooBar.

like and not like in mysql query

3 Answers3

Example