--- title: "PostgreSQL LIKE Operator: A Detailed Guide | CoderPad" description: "When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator." canonical: "https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/" last_modified: "2026-04-15 16:20:49" --- [Back to blog](https://coderpad.io/blog/) # PostgreSQL LIKE Operator: A Detailed Guide April 17, 2023 2 Minute Read By Carlos Schults [Development](https://coderpad.io/blog/development/) When using databases, searching for values that match a given pattern is a familiar necessity. You might want to retrieve all students whose last name starts with a given letter or update all products whose ids include a particular string. If you’re a PostgreSQL user, you will use the PostgreSQL`LIKE`operator for that. If you want to learn more about PostgreSQL`LIKE`, you’ve come to the right place, since this post is all about this operator. By the end of the post, you’ll have learned: - what PostgreSQL`LIKE`does, and why you’d want to use it - how it differs from the`LIKE`operators in different database engines - how it differs from the`ILIKE`operator To paraphrase that famous movie, though, only theory and no practice make this a dull post. So, before wrapping up, we’ll walk you through several examples of the`LIKE`operator in practice. Requirements If you want to follow along with the practical portion of the post, keep in mind that the post assumes the following: - there is a functional instance of PostgreSQL installed on your machine - you can connect to the said instance through a client - you have at least a bit of experience with the PostgreSQL database and the SQL language Instead of actually installing PostgreSQL, I recommend using the [](https://hub.docker.com/_/mysql) [CoderPad MySQL Sandbox](https://coderpad.io/languages/mysql/) to quickly and easily get started writing SQL as it’ll be your MySQL client for this article. PostgreSQL`LIKE`: The Fundamentals With the requirements out of the way, let’s start by covering some fundamentals about today’s topic. What Is PostgreSQL`LIKE`? PostgreSQL`LIKE`is PostgreSQL’s implementation of the`LIKE`operator from the SQL language. You use LIKE when you want to retrieve rows in which one or more of their textual columns match a given pattern. In the introduction, I gave an example: retrieving students whose last name starts with a given letter. So, let’s see a real query that does just that using PostgreSQL`LIKE`: ``` SELECT id, first_name, last_name, email FROM students WHERE last_name LIKE 'M%';Code language: SQL (Structured Query Language) (sql) ``` The query above retrieves some columns from the`students`table, but only the rows which match the condition: the value from the`last_name`column has to start with the letter “M”. There are some interesting points to notice from this first example: - You use the`LIKE`operator in the` [WHERE](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/)`clause of a query - It goes on the same spot you’d put any comparison operator such as “==”, “>”, “<“, and so on - The percentage character (%) is used as a wildcard to match any number of characters What if you wanted only students whose first name ended with a “k”? Simple, just swap the letter and the wildcard: ``` SELECT id, first_name, last_name, email FROM students WHERE first_name LIKE '%k';Code language: SQL (Structured Query Language) (sql) ``` Another example: let’s match students whose email addresses contain the word “gmail”: ``` SELECT id, first_name, last_name, email FROM students WHERE email LIKE '%gmail%';Code language: SQL (Structured Query Language) (sql) ``` That’s right: if you want to match values that contain a given string, you surround the string with percentage signs (`%`). What Is the Difference Between LIKE and ILIKE in Postgresql? When reviewing PostgreSQL code in the wild, you might encounter queries that use the`ILIKE`operator instead of`LIKE`and get confused. The difference is that the`LIKE`operator is case sensitive—i.e. it distinguishes between lowercase and uppercase letters. On the other hand, the`ILIKE`operator is case insensitive —hence the i. It does not distinguish between upper and lowercase letters, and therefore you should use it when you don’t care about the case. An important thing to keep in mind: the`ILIKE`operator doesn’t exist in ANSI SQL; instead, it’s a specific extension of PostgreSQL. That means that employing ILIKE makes your SQL code less portable, in case you have the need to change your database engine. PostgreSQL Like: Practical Use Cases Having covered the basics of PostgreSQL`LIKE`, let’s now see several usage examples. We’ll begin by preparing the database for the tests. Preparing the Database Using your preferred client, connect to your PostgreSQL instance, making sure there’s a database you can connect to for the tests. Let’s start by creating a table and inserting some rows into it: ``` CREATE TABLE albums ( id SERIAL PRIMARY KEY, title VARCHAR(250) NOT NULL, artist VARCHAR(250) NOT NULL, release_year int NOT NULL); INSERT INTO albums (title, artist, release_year) VALUES('Abbey Road', 'The Beatles', 1969); INSERT INTO albums (title, artist, release_year) VALUES('Construção', 'Chico Buarque', 1971); INSERT INTO albums (title, artist, release_year) VALUES('The Dark Side of the Moon', 'Pink Floyd', 1973); INSERT INTO albums (title, artist, release_year) VALUES('Reckless', 'Bryan Adams', 1984); INSERT INTO albums (title, artist, release_year) VALUES('Ryan Adams', 'Ryan Adams', 2014);Code language: SQL (Structured Query Language) (sql) ``` Matching a Single Character As you’ve seen, the table now contains albums by both Bryan and Ryan Adams. That’s not by coincidence. First, use the following query to retrieve albums by the two singers: ``` SELECT * FROM albums WHERE artist LIKE '%yan%';Code language: SQL (Structured Query Language) (sql) ``` The query above matches all artists which contain the string “yan” anywhere in their name. Here’s what the result looks like to me: ``` id | title | artist | release_year ----+------------+-------------+-------------- 4 | Reckless | Bryan Adams | 1984 5 | Ryan Adams | Ryan Adams | 2014 (2 rows)Code language: SQL (Structured Query Language) (sql) ``` The`LIKE`operator offers an additional wildcard, the underscore character (_) which matches a single character. That way, it’s possible to rewrite the query in such a way that only Ryan Adams is returned: ``` SELECT * FROM albums WHERE artist LIKE '_yan%';Code language: SQL (Structured Query Language) (sql) ``` And here, again, is the result: ``` id | title | artist | release_year ----+------------+------------+-------------- 5 | Ryan Adams | Ryan Adams | 2014 (1 row)Code language: SQL (Structured Query Language) (sql) ``` Negative Matches When working with database tables, you often need to retrieve data that doesn’t match a given pattern. For that, you can use the`NOT LIKE`operator. For instance, let’s get only artists that don’t start with “The”: ``` SELECT * FROM albums WHERE artist NOT LIKE 'The%';Code language: JavaScript (javascript) ``` Since the only artist starting with “The” are The Beatles, they won’t be fetched by the query above. Matching Without Case Sensitivity As mentioned earlier, the`ILIKE`operator is a special PostgreSQL extension to`LIKE`that matches in a case-insensitive way. Let’s see that in practice. First, let’s use the`LIKE`operator to get all artists which contain an uppercase “A”: ``` SELECT * FROM albums WHERE artist LIKE '%A%';Code language: SQL (Structured Query Language) (sql) ``` Only “Bryan Adams” and “Ryan Adams” are returned, which makes sense: ``` id | title | artist | release_year ----+------------+-------------+-------------- 4 | Reckless | Bryan Adams | 1984 5 | Ryan Adams | Ryan Adams | 2014 (2 rows)Code language: SQL (Structured Query Language) (sql) ``` Let’s now replace the`LIKE`operator with the`ILIKE`one and rerun the query: ``` SELECT * FROM albums WHERE artist ILIKE '%A%'; id | title | artist | release_year ----+------------+---------------+-------------- 1 | Abbey Road | The Beatles | 1969 2 | Construção | Chico Buarque | 1971 4 | Reckless | Bryan Adams | 1984 5 | Ryan Adams | Ryan Adams | 2014 (4 rows)Code language: JavaScript (javascript) ``` Now, all artists are returned, except for Pink Floyd. Escaping the WildCards For this example, let’s update two rows: ``` UPDATE albums SET artist = 'The%Beatles' WHERE id = 1; UPDATE albums SET artist = 'Chico_Buarque' WHERE id = 2;Code language: SQL (Structured Query Language) (sql) ``` Now, let’s say we want to retrieve albums from artists whose name contains a percentage sign or an underscore: ``` SELECT * FROM albums WHERE artist LIKE '%%%'; SELECT * FROM albums WHERE artist LIKE '%_%';Code language: SQL (Structured Query Language) (sql) ``` As you’ve probably imagined, both queries don’t work: they return all rows. Fortunately, there’s a way around this problem: it’s possible to escape a wildcard so we can use it as a normal character. You simply put a backslash before the offending character: ``` SELECT * FROM albums WHERE artist LIKE '%\%%';Code language: SQL (Structured Query Language) (sql) ``` And here’s the result: ``` id | title | artist | release_year ----+------------+---------------+-------------- 1 | Abbey Road | The Beatles | 1969 (1 rows)Code language: SQL (Structured Query Language) (sql) ``` The same would work for the underscore character. But before wrapping up, let’s complicate things just a bit more—because, why not? First, let’s update the “Abbey Road” row once more: ``` UPDATE albums SET artist = 'The\Beatles' WHERE id = 1;Code language: SQL (Structured Query Language) (sql) ``` Now, in order to use LIKE to retrieve a row containing the backslash character, you’d have to write this query: ``` SELECT * FROM albums WHERE artist LIKE '%\\%';Code language: JavaScript (javascript) ``` You have to escape the escape character. It’s not that terrible, but you might want to write a more readable version. If that’s the case, it’s possible to choose a different character as the escape character: just use the`ESCAPE`clause: ``` SELECT * FROM albums WHERE artist LIKE '%$\%' ESCAPE '$';Code language: JavaScript (javascript) ``` With the`ESCAPE`clause, you can choose a different character as the escape character. That way—at least in my opinion—the resulting query is more explicit in the way it escapes the wildcard. Experiment with the`LIKE`operator in the sandbox below: Conclusion: PostgreSQL LIKE: Learn It, Leverage It In this post, you’ve learned about the LIKE operator in PostgreSQL: what it is, what’s used for, and how it works along several examples. As you’ve seen, the operator’s working is quite easy once you understand how the wildcards work. We’ve also walked you through some usage examples, like matching against a single character and performing negative matches. Where should you go now? For starters, continue to learn about pattern matching on PostgreSQL. Here are some topic suggestions for you: - `SIMILAR TO`, a more recent clause - the`substring()`function - the`regexp_like()`function Additionally, the site you’re on right now has great resources for people who want to learn more not only about PostgreSQL but databases in general. Here are some posts that are worth your time: - [How to Get Metadata from PostgreSQL System Catalogs](https://coderpad.io/blog/development/postgresql-system-catalogs-metadata/) - [Why & How to Use Window Functions to Aggregate Data in Postgres](https://coderpad.io/blog/development/window-functions-aggregate-data-postgres/) - [How to Interact with Databases using SQLAlchemy with PostgreSQL](https://coderpad.io/blog/development/sqlalchemy-with-postgresql/) - [SQL Functions and Techniques Every Data Person Should Know](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/) Thanks for reading, and until next time! ## Subscribe for more CoderPad insights ## Trending Articles Previous slide Next slide [Development ## What is Prompt Engineering? Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post. Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development ## A Guide To Using React’s useCallback() Hook Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together. Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development ## MySQL vs. PostgreSQL: How Do They Compare? A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you. Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development ## How To Use DataProvider In TestNG With Examples When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG. Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development ## How To Get Up And Running With Django Migrations: A Guide Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects. Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development ## Writing A Parameterized Test In JUnit With Examples A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide. Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development ## How To Use Assert In JUnit With Examples The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples. Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development ## A Simple Guide To TypeORM: What It Is And How To Use It Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is. Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development ## Using PreparedStatement in JDBC With Examples In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code. Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development ## React vs React Native: The Differences Explained React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice. Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development ## Logging in Django The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article. Read the post](https://coderpad.io/blog/development/logging-in-django/) [Development ## How To Get Started With TypeScript in Node.js TypeScript was the third most popular programming language in 2022, following Rust and Python. It’s widely admired by the JavaScript community and used by many companies to build front-end and back-end applications. In this post, let's dive into TypeScript, learn how to get started with TypeScript in a Node.js application, and then cover ts-node. Read the post](https://coderpad.io/blog/development/how-to-get-started-with-typescript-in-node-js/) ## Hire with confidence. See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows. [Request a demo](https://coderpad.io/sales/)