SQL LIKE Operators
FREE
The SQL LIKE operator within a WHERE clause serves to search for a specific pattern within a column.
Two commonly utilized wildcards in association with the SQL LIKE operator are:
- The percent sign (%), which signifies zero, one, or multiple characters.
- The underscore sign (_), representing a single character.
Example 1:
This query will return all rows from the students table where the student_name column starts with "A", followed by zero or more characters.
Example 2:
This query retrieves rows from the "words" table where the "word" column contains exactly three characters, with "e" positioned as the second character, and any characters occupying the first and third positions.
Syntax
What are wild cards?
SQL wildcards are unique symbols utilized within SQL queries to identify patterns in data. When paired with the LIKE operator in MySQL database, the following wildcards are employed:
| Wildcard characters | Description |
|---|---|
| % | The percent sign (%) in SQL serves to denote the presence of zero, one, or multiple characters in a pattern. |
| _ | The underscore (_) in SQL denotes the existence of a single character or number within a pattern. |
Examples
| Expression | Description |
|---|---|
| 'abc%' | Find any values that start with "abc" |
| '%xyz%' | Find any values that contain "xyz" in any position |
| '_def%' | Find any values that have "def" in the second, third, and fourth positions |
| 'm_%_%' | Find any values that start with "m" and have at least 4 characters |
| '%ghi' | Find any values that end with "ghi" |
| '_j%l' | Find any values that have "j" in the second position and end with "l" |
| 'abc____xyz' | Find any values that are exactly 8 characters long and start with "abc" and end with "xyz" |
Like Operator in SQL with OR
The SQL LIKE operator allows us to specify multiple string patterns for selecting rows by utilizing the OR operators.
Syntax
Example
Consider the details of following students of student table :
| ID | Student_Name | Marks |
|---|---|---|
| 1 | Sameer | 95 |
| 2 | Shyam | 90 |
| 3 | Pinki | 88 |
| 4 | Soni | 80 |
| 5 | Samar | 67 |
Output
The SQL query retrieves all rows from the 'student' table where the 'Student_name' column either starts with 'S', followed by any character(s), and ends with 'r', or starts with 'P', followed by any character(s), and ends with 'i'.
| ID | Student_name | Marks |
|---|---|---|
| 1 | Sameer | 95 |
| 3 | Pinki | 88 |
| 5 | Samar | 67 |
NOT operator with the LIKE condition
The NOT operator in conjunction with the LIKE operator is utilized to retrieve rows that do not contain a specific string as defined in the search pattern.
Syntax
Example
Consider the details of following students of student table :
| ID | Student_name | Marks |
|---|---|---|
| 1 | Sameer | 95 |
| 2 | Shyam | 90 |
| 3 | Pinki | 88 |
| 4 | Soni | 80 |
| 5 | Samar | 67 |
Ouput
The SQL query retrieves all rows from the 'student' table where the 'Student_name' column does not contain string of any length whose second character is 'a'.
| ID | Student_name | Marks |
|---|---|---|
| 2 | Shyam | 90 |
| 3 | Pinki | 88 |
| 4 | Soni | 80 |
Escape characters with LIKE operator
The escape character in SQL serves the purpose of excluding specific wildcard characters from the interpretation of the LIKE operator. This enables the utilization of these characters in their literal sense. Additionally, it allows for the avoidance of reserved SQL syntax characters, such as the single quote ('), percent sign (%), and underscore (_). For instance, when needing to search for the '%' character as a literal in a LIKE condition, the escape character facilitates this functionality.
Syntax
- pattern denotes the specific pattern you aim to match within a query.
- ESCAPE serves as the keyword signaling the introduction of an escape character.
- escape_character designates the character chosen to function as the escape character within the query.
Example
Suppose the given below student table
| Student_name | Marks_Percentage |
|---|---|
| Adarsh | 95.3% |
| Aman | 97.6 |
| Raghav | 56.7% |
| Vishal | 44 |
We are displaying all the records from the student table, where the Marks_Percentage contains the % literal.
Output
| Student_name | Marks_Percentage |
|---|---|
| Adarsh | 95.3% |
| Raghav | 56.7% |
An escape character is typically designated as a single character used to indicate that the following character should be treated differently, often in programming languages or textual formats. It is advisable to select a character that is not commonly found in the data to avoid confusion or misinterpretation.
Conclusion
- The SQL LIKE operator offers powerful capabilities for pattern matching within column data, enabling flexible search functionalities in queries.
- With the percent sign (%) and underscore (_) as wildcards, SQL LIKE facilitates searches for patterns with variable and fixed character positions, respectively.
- The inclusion of escape characters enables the interpretation of wildcard characters as literals, enhancing the accuracy of pattern matching and allowing for the search of special characters like '%' within the data.
- The SQL LIKE operator can be combined with OR operators to broaden search criteria, offering greater flexibility in querying datasets with diverse patterns.
- The NOT operator with LIKE enables exclusionary searches, useful for retrieving records that do not match a specified pattern, further refining query results.