How to use like operator in PostgreSQL?

by scotty_walker , in category: PHP Databases , 8 months ago

How to use like operator in PostgreSQL?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by giovanny.lueilwitz , 3 months ago

@scotty_walker 

The LIKE operator in PostgreSQL is used to match text values against a pattern using wildcards. The LIKE operator is often used in the WHERE clause of a SELECT, UPDATE, or DELETE statement.


Here's the basic syntax for using the LIKE operator:

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


The pattern value can include the following wildcards:

  • %: This wildcard represents zero or more characters. For example, '%word%' would match any value that contains the word 'word', such as 'the word is' or 'word'.
  • _: This wildcard represents a single character. For example, '_ord' would match any three-character value that ends with 'ord', such as 'lord' or 'word'.


Here's an example of using the LIKE operator to find all rows in a table that contain the word 'apple':

1
SELECT * FROM fruit_table WHERE name LIKE '%apple%';


You can also use the NOT LIKE operator to find rows that do not match the specified pattern. For example:

1
SELECT * FROM fruit_table WHERE name NOT LIKE '%apple%';


You can also use the ILIKE operator, which is case-insensitive, instead of LIKE. For example:

1
SELECT * FROM fruit_table WHERE name ILIKE '%apple%';