How to use not like operator in PostgreSQL?

by hal.littel , in category: PHP Databases , 2 years ago

How to use not like operator in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

2 answers

Member

by gilbert , a year ago

@hal.littel 

In PostgreSQL, you can use the NOT LIKE operator to find rows that do not match a specific pattern.


Here is the basic syntax for using NOT LIKE in a SELECT statement:

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;


For example, suppose you have a table called customers with a name column. To select all rows from the customers table where the name does not contain the string 'John', you can use the following query:

1
SELECT * FROM customers WHERE name NOT LIKE '%John%';


You can also use the NOT LIKE operator with the ESCAPE clause to specify an escape character for the pattern. For example:

1
SELECT * FROM customers WHERE name NOT LIKE '%John%%' ESCAPE '';


This will select all rows where the name column does not contain the string 'John%'. The character is used as the escape character, so it tells PostgreSQL to treat the % character as a literal character and not as a wildcard.


I hope this helps! Let me know if you have any questions.

Member

by darion , 7 months ago

@hal.littel 

The LIKE and NOT LIKE operators in PostgreSQL are used to compare a value to a pattern using wildcard characters. The basic syntax for using the NOT LIKE operator in PostgreSQL is:


SELECT column1, column2, ... FROM table_name WHERE column1 NOT LIKE pattern;


The pattern can include the following wildcard characters:

  • %: Matches any sequence of characters (including zero characters).
  • _: Matches any single character.


For example, if you have a table called employees with a first_name column, and you want to select all rows where the first name does not start with 'J', you can use the following query:


SELECT * FROM employees WHERE first_name NOT LIKE 'J%';


This will select all rows where the first_name column does not start with the letter 'J'. You can also use the % wildcard to match any characters after 'J'.


You can combine the % wildcard with other characters to create more complex patterns. For example, if you want to select all rows where the first name does not contain 'ohn', you can use the following query:


SELECT * FROM employees WHERE first_name NOT LIKE '%ohn%';


This will select all rows where the first_name column does not contain the string 'ohn' anywhere in it.


Remember that the NOT LIKE operator is case-sensitive. If you want to perform a case-insensitive search, you can use the ILIKE operator instead.


I hope this clarifies how to use the NOT LIKE operator in PostgreSQL. Let me know if you have any further questions!