PostgreSQL

In PostgreSQL, DQL (Data Query Language) is used to retrieve data from the database, and one of the commonly used operators in this context is the NOT LIKE operator. The NOT LIKE operator is utilized to filter records that do not match a specific pattern. It is often used with wildcard characters such as%(which represents zero or more characters) and _ (which represents a single character). This can be especially useful when you want to exclude certain values from your query results.

For example, in a banking database with tables likecustomers, accounts, and transactions, you might use the NOT LIKE operator to find customers whose names do not contain a particular substring.

Example Code:

  SELECT customer_name
  FROM customers
  WHERE customer_name NOT LIKE '%John%';

This query will return the names of customers that do not contain "John" anywhere in their name.

Key Points on theNOT LIKE Operator:

1. Basic Syntax:
   • TheNOT LIKEoperator is used in the WHEREclause.
   • It can be combined with wildcard characters for pattern matching.

   Example:

     SELECT * FROM accounts
     WHERE account_type NOT LIKE 'Savings%';
   

   This will return accounts whose type does not start with "Savings".

2. Using Wildcards:
   • %represents zero or more characters.
   • _represents exactly one character.

   Example:

     SELECT * FROM transactions
     WHERE transaction_id NOT LIKE 'T%1';
   

   This will return transactions where the transaction_iddoes not start with "T" and end with "1".

3. Combining with Other Conditions:
   • You can combine the NOT LIKEoperator with other conditions using AND, OR, etc.

   Example:

     SELECT * FROM customers
     WHERE customer_name NOT LIKE 'M%'
     AND city = 'New York';
   

   This query will return customers from New York whose names do not start with "M".

4. Case Sensitivity:
   • In PostgreSQL, the LIKE and NOT LIKE operators are case-sensitive.
   • To perform a case-insensitive search, you can use the ILIKE operator instead.

   Example:

     SELECT * FROM customers
     WHERE customer_name NOT LIKE 'john%';  -- Case-sensitive
     SELECT * FROM customers
     WHERE customer_name NOT ILIKE 'john%'; -- Case-insensitive
   

5. Practical Usage in Banking Systems:
   • Exclude certain account types from reports.
   • Filter out customers or transactions that match unwanted patterns.
   • Find records that do not contain certain strings, useful in cleanup operations.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT *
FROM org_client
WHERE last_name NOT LIKE 'M%';

SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';

Example 1 - Raw data from client table

Example 1 - Query

SELECT *
FROM org_client
WHERE last_name NOT LIKE 'M%';

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Retrieve entries for clients whose first names do not conclude with '%e'.

Example 2 - Query

SELECT *
FROM org_client
WHERE first_name NOT LIKE '%e';

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output