- 4 years ago
- Zaid Bin Khalid
- 2127 Views
-
4
In this session, we are going to learn how to use the SQL LIKE operator.
SQL LIKE Operator.
The SQL LIKE operator is used as a logical operator who helps you to find the match and similar values rather than exact ones.
Wild card characters.
- The percent sign (%)
- The underscore (_)
In SQL LIKE operator, where:
- (%) percent sign represented as zero, one, or multiple characters.
- The underscore represents a single number or character.
Pattern Matching.
The SQL Pattern is a set of characters that help to find in the column or expression. It can include the following valid wildcard characters.
- The percent wildcard (%): any string of zero or more characters.
- The underscore (_) wildcard: any single character.
- The [list of characters] wildcard: any single character within the specified set.
- The [character-character]: any single character within the specified range.
- The [^]: any single character not within a list or a range.
| LIKE Operator | Description |
|---|
| WHERE CustomerName LIKE ‘a%’ | Finds any values that start with “a” |
| WHERE CustomerName LIKE ‘%a’ | Finds any values that end with “a” |
| WHERE CustomerName LIKE ‘%or%’ | Finds any values that have “or” in any position |
| WHERE CustomerName LIKE ‘_r%’ | Finds any values that have “r” in the second position |
| WHERE CustomerName LIKE ‘a_%’ | Finds any values that start with “a” and are at least 2 characters in length |
| WHERE CustomerName LIKE ‘a__%’ | Finds any values that start with “a” and are at least 3 characters in length |
| WHERE ContactName LIKE ‘a%o’ | Finds any values that start with “a” and ends with “o” |
Wildcard characters are used to make SQL LIKE operator more flexible during string comparison operator such as equal and not equal
Now here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards. Let suppose we have an employee’s table in our database with the following records.
+--------+------------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+------------------+------------+--------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
| 6 | simons bistro | 2009-04-01 | 6000 | 1 |
+--------+------------------+------------+--------+---------+
Now, let’s assume, in order to find out all the employees whose name initiated with S letter. Use the query stated below.
SELECT * FROM employees WHERE emp_name LIKE 'S%';
The executing results of the stated query will display results such as.
+--------+------------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+------------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 6 | simons bistro | 2009-04-01 | 6000 | 1 |
+--------+------------------+------------+--------+---------+
The non-binary SQL string is case sensitive, that means if you want to search with WHERE name LIKE ‘S%,’ you will get all column values that start with S or s (as in our output, you might notice we have both “Sarah” and “Simon’s”). Though, if you still want to make your search output as more case sensitive, you can either use the BINARY operator in the query as follow.
SELECT * FROM employees WHERE BINARY emp_name LIKE 'S%';
The executing results of the stated query will display results that will return the specific names of those employees whose names are initiated with capital letter S.
+--------+------------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+------------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
+--------+------------------+------------+--------+---------+
- 4 years ago
- Zaid Bin Khalid
- 2127 Views
-
4