PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References
PostgreSQL Tutorial PostgreSQL Advanced PostgreSQL Database Account Management PostgreSQL References

PostgreSQL - NOT ILIKE Keyword



The PostgreSQL NOT ILIKE keyword is the negation of ILIKE keyword. The ILIKE keyword is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the ILIKE or NOT ILIKE keyword are given below:

Note: The ILIKE keyword is similar to LIKE keyword except the ILIKE keyword performs case-insensitive match according to the active locale and LIKE keyword performs case-sensitive match according to the active locale.

Wildcard Characters in PostgreSQL

SymbolDescriptionExample
%Represents zero, one or multiple characters.'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_Represents one character.'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
ESCAPEAllow test for literal instances of a wildcard character such as % or _'J%!%' ESCAPE '!' represents a value that starts with J and ends in %, for example - John%, Jo% and Journey% etc.

Note: The operator ~~* is equivalent to ILIKE. Similarly, !~~* operator represents NOT ILIKE.

Syntax

The syntax for using ILIKE and NOT ILIKE keywords in PostgreSQL are given below:

/* Using ILIKE keyword */
SELECT column1, column2, ...
FROM table_name
WHERE column ILIKE pattern;

/* Using NOT ILIKE keyword */
SELECT column1, column2, ...
FROM table_name
WHERE column NOT ILIKE pattern;

The table below describes patterns which is used with ILIKE keyword and uses (%) and (_).

PatternDescription
'J%'A value that start with "J".
'%n'A value that end with "n".
'%oh%'A value that have "oh" in any position.
'_o%'A value that have "o" in the second position.
'J_%'A value that start with "J" and have at least 2 characters.
'J__%'A value that start with "J" and have at least 3 characters.
'J%n'A value that start with "J" and ends with "n".

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • Using the % Wildcard : To select all records of the Employee table with Name starting with 'Jo', the query is given below.

    SELECT * FROM Employee
    WHERE Name ILIKE 'Jo%';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
  • Using the % Wildcard with NOT ILIKE keyword: NOT ILIKE keyword is used as the negation of ILIKE keyword. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:

    SELECT * FROM Employee
    WHERE Name NOT ILIKE 'Jo%';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800
  • Using the _ Wildcard : To select all records of the Employee table with Name containing 'o' as second character, the query is mentioned below.

    SELECT * FROM Employee
    WHERE Name ILIKE '_o%';
    

    The result of the above code will be:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
  • Using the _ Wildcard with NOT ILIKE keyword: To select all records of the Employee table with Name not containing 'o' as second character, the query is given below:.

    SELECT * FROM Employee
    WHERE Name NOT ILIKE '_o%';
    

    The result of the above code will be:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800

❮ PostgreSQL - Keywords