what is like in sql?

The SQL LIKE operator is used with the WHERE clause to get a result set that matches the given string pattern. It has the following syntax:

Here,

For example,

Here, the SQL command selects customers whose country is UK.

Note: Although the LIKE operator behaves similarly to the = operator in this example, they are not the same. The = operator is used to check equality whereas LIKE operator is used to match string patterns only.

The SQL LIKE query is often used with wildcards to match a pattern of string. For example,

Here, % (means zero or more characters) is a wildcard character. Hence, the SQL command selects customers whose last_name starts with R followed by zero or more characters after it.

There are more wildcard characters we can use with LIKE. Let's look at another example using the _ wildcard character.

Here, the SQL command selects customers whose country name starts with U followed by exactly one character.

We can also invert the working of the LIKE operator by using the NOT operator with it. This returns a result set that doesn't match the given string pattern. The SQL NOT LIKE operator has the following syntax:

Here,

For example,

Here, the SQL command selects all customers except those whose country is USA.

The SQL LIKE is a logical operator that is used to retrieve the data in a column of a table, based on a specified pattern.

It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using ‘Wildcards’.

Suppose we need to submit the list of all the students whose name starts with ‘K’. We can obtain this with the help of the LIKE operator as follows −

Here, the “%” is a wild card which represents zero, one or multiple characters. And the expression “K%” specifies that it will display the list of all the students whose name starts with ‘k’.

The basic syntax of the SQL LIKE operator is as follows −

SQL wildcards are special characters used in SQL queries to match patterns in the data. Following are the four wildcards used in conjunction with the LIKE operator −

Note − In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ‘%’ and ‘_’.

The table given below has a few examples showing the WHERE clause having different LIKE operators with '%' , '_' , and pattern −

The % sign represents zero or multiple characters. The ‘%’ wildcard matches any length of a string which even includes the zero length.

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

Now, insert values into this table using the INSERT statement as follows −

The table will be created as follows −

Now, let us try to display all the records from the CUSTOMERS table, where the SALARY starts with 200.

This would produce the following result −

Below is the query, that displays all the records from the CUSTOMERS table previously created, with the NAME that has ‘al’ in any position. Here we are using multiple ‘%’ wildcards in the LIKE condition −

The following result is produced −

The underscore wild card represents a single number or character. A single ‘_’ looks for exactly one character similar to the ‘%’ wildcard.

Following is the query, which would display all the records from the CUSTOMERS table previously created, where the Name starts with K and is at least 4 characters in length −

The result obtained is given below −

Following is the query to display all the records from the CUSTOMERS table, where the Name has ‘m’ in the third position −

We get the following result on executing the above query −

The square bracket with a list of characters matches any single character within the given range or set .

In the query given below we are trying to display all the records from the CUSTOMERS table, where the NAME starts with K and has the specified characters set −

We get the following result −

In here, we are displaying all the records from the CUSTOMERS table, where the first and last character in the NAME lies anywhere in the range −

The result given below is displayed −

The matches any single character that is not present in the given range or character list.

In the query given below, the first character in the NAME column is not in the range of −

The following result is obtained −

We can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.

Following is the basic syntax of using LIKE operator with OR operator −

Here, the SQL command select the customers whose NAME starts with C and ends with i, or customers whose NAME ends with k −

This will produce the following result −

We use the NOT LIKE operator to extract the rows which does not contain a particular string provided in the search pattern.

Following is the basic syntax of NOT LIKE operator in SQL −

In the query given below we are trying to fetch all the customers whose NAME does not start with K −

This will produce the following result −

The escape character in SQL is used to exclude certain wildcard characters from the expression of the LIKE operator. By doing so, we can use these characters in their general sense.

Using escape, we can also avoid using the characters that are reserved in SQL syntax to denote specific commands, such as the single quote “ ' ”, “%” and “_”.

For example, if you need to search for “%” as a literal in the LIKE condition, then it is done using Escape character.

Note − An escape character is only defined as a single character. It is suggested to choose the character which is not present in our data.

The syntax for using the LIKE operator with escape characters is as follows −

Where,

Let us create a new table EMPLOYEE using the query below −

Now, we can insert values into this empty tables using the INSERT statement as follows −

The Employee table consists of the salary of employees in an organization and the bonus percentage in their salary as shown below −

Now, let us try to display all the records from the EMPLOYEE table, where the BONUS_PERCENT contains the % literal.

This will produce the following result −

In here, we are trying to return the BONUS_PERCENT that starts with ‘2’ and contains the ‘%’ literal.

Following result is obtained −

The few uses of LIKE operators are given below −

LIKE and = are different operators. Most answers here focus on the wildcard support, which is not the only difference between these operators!

= is a comparison operator that operates on numbers and strings. When comparing strings, the comparison operator compares whole strings.

LIKE is a string operator that compares character by character.

To complicate matters, both operators use a collation which can have important effects on the result of the comparison.

Let us first identify an example where these operators produce obviously different results. Allow me to quote from the MySQL manual:

Please note that this page of the MySQL manual is called String Comparison Functions, and = is not discussed, which implies that = is not strictly a string comparison function.

The SQL Standard § 8.2 describes how = compares strings:

(Emphasis added.)

What does this mean? It means that when comparing strings, the = operator is just a thin wrapper around the current collation. A collation is a library that has various rules for comparing strings. Here is an example of a binary collation from MySQL:

This particular collation happens to compare byte-by-byte (which is why it's called "binary" — it doesn't give any special meaning to strings). Other collations may provide more advanced comparisons.

For example, here is a UTF-8 collation that supports case-insensitive comparisons. The code is too long to paste here, but go to that link and read the body of my_strnncollsp_utf8mb4(). This collation can process multiple bytes at a time and it can apply various transforms (such as case insensitive comparison). The = operator is completely abstracted from the vagaries of the collation.

The SQL Standard § 8.5 describes how LIKE compares strings:

(Emphasis added.)

This is pretty wordy, so let's break it down. Items ii and iii refer to the wildcards _ and %, respectively. If P does not contain any wildcards, then only item iv applies. This is the case of interest posed by the OP.

In this case, it compares each "substring" (individual characters) in M against each substring in P using the current collation.

The LIKE command is used in a WHERE clause to search for a specified pattern in a column. You can use two wildcards with LIKE : % - Represents zero, one, or multiple characters. _ - Represents a single character (MS Access uses a question mark (?) instead)