How to Use “NOT LIKE” in Query using Query function

Google Sheets does not have a direct "NOT LIKE" option within the QUERY function as it uses a custom SQL language called Google Visualization API Query Language. However, you can simulate a "NOT LIKE" condition by using the "!=" operator and some creativity. Here's how you can do that:

1. Use the QUERY function: Start with the basic QUERY function to retrieve data from the desired range. The syntax for the QUERY function is:

=QUERY(data, query, [headers])

where data is your range of data, query is the SQL-like query you want to apply, and headers is an optional parameter that indicates how many header rows your data has.

Example:

=QUERY(A1:C10, "SELECT * WHERE B != 'John'", 1)

This query returns all rows from the range A1:C10 where the value in column B is not 'John'.

2. Simulate "NOT LIKE" using "!=" and wildcards: To filter the data based on a partial match, you can use the "!=" operator along with the "*" wildcard character.

Example:

=QUERY(A1:C10, "SELECT * WHERE B != '*ohn'", 1)

This query returns all rows from the range A1:C10 where the value in column B does not end with 'ohn'.

However, this method has a limitation: it works only when the text you want to exclude is at the beginning or end of the values in the specified column.

3. Use REGEXMATCH function to simulate "NOT LIKE": A more powerful approach is to use the REGEXMATCH function to apply a regular expression pattern to your data. This method will return TRUE if the pattern matches, and FALSE otherwise.

Example:

=QUERY(A1:C10, "SELECT * WHERE NOT(REGEXMATCH(B, '.*ohn.*'))", 1)

This query returns all rows from the range A1:C10 where the value in column B does not contain 'ohn' anywhere in the text.

Remember to replace the range and the pattern you want to exclude according to your needs.