[SQL] How to use wildcards in a LIKE operator
When we want to filter a database and only get the records that have a specific string, we use the LIKE operator. To do that, we have to use wildcards to tell the program which strings to choose. In this article, I will explain what is and how to use wildcards in a LIKE operator, and you will be able to filter the database with any strings you want.
Table of Contents
What is a wildcard?
A wildcard is a special letter that represents specific strings, and it is used in programs or used when we use Unix commands. For example, in SQL, there are 2 mainly used wildcards: one is “%”, and the other is “_”. The wildcard “%” represents any strings including an empty string, and “_” represents any single characters but it doesn’t include an empty character.
The wildcards “%” and “_”, which we use in SQL, are “*” and “?” in Unix commands.
Because “%” represents any strings, for example, “ab%fgh” matches “abfgh”, “abcdefg”, “abhellofgh” and so on. On the other hand, “ab_fgh” maches, for example, “abcfgh”, “abXfgh”, or “ab9fgh” because “_” represents any single characters.
|SQL||Unix||description||use case||matching strings|
|%||*||represents any strings including an empty string||ab%fg||abfg, abLLLfg, ab2fg|
|_||?||represents any single characters||S_L||SAL, S-L, S5L|
What is the LIKE operator?
In SQL, we can use the LIKE operator when we want to only get the data that have specific strings from the database. For example, using the LIKE operator, we can get all of the data that include “abc” in its string. Or we can get all of the data that starts or ends with “abc”.
In the LIKE operator, we use the wildcards explained in the previous section to specify the target string.
How to use wildcards with the LIKE operator?
We can use the LIKE operator like this.
SELECT “column1” FROM “table” WHERE “column2” LIKE “target string”;
With this command, we can get the records whose column2 satisfies the target string. For example, if we use LIKE “my %”, the query chooses any records whose column2 starts from “my “. Or, if we want to get data that include “computer”, we can use LIKE “% computer %”.