Thứ Sáu, Tháng Hai 7, 2025
spot_img
HomeLIKE command in SQL - QuanTriMang.com

LIKE command in SQL – QuanTriMang.com

Like in SQL What is that? How to use the Like function in SQL how? Let's find out with Quantrimang!

SQL is certain knowledge you need to know when learning programming in addition to other popular languages. SQL helps you program and manage databases better. Basically, learning SQL is not too difficult if you take the time to learn it seriously.

SQL also has functions, commands and operators as programming languages. In this article, we will learn together the LIKE command in SQL!

In SQL, operator LIKE used to compare a value with similar values ​​using wildcard operators. Operator LIKE often used in clauses WHERE to search for a specified pattern in a column.

There are 2 wildcard characters used in the LIKE operator, these characters can be used in combination with each other:

  • Percent (%): Represents a string of characters of any length (including length 0).
  • Underscore (_): Represents a single character.

Note: MS Access uses asterisks

instead of using the percent sign (%); and the question mark (?) replaces the underscore (_).

Syntax of LIKE command in SQL

SELECT cot1, cot2,.... cotn
FROM ten_bang
WHERE ten_cot LIKE [mẫu so sánh];

The basic syntax of the LIKE command is as follows:Tips AND : You can also combine any number of conditions using operators ORor

.

Here are some examples showing different LIKE operators, using a combination of wildcards '%' and '_': Comparison sample
DescribeLIKE 'qtm

%'Find values ​​starting with “qtm

LIKE '%qtm

'Find values ​​ending with “qtm

LIKE '%qtm

%'Find values ​​with “qtm

” in any locationLIKE '_qtm

%'Find values ​​with “qtm

” in second placeLIKE 'q

_%'Find values ​​starting with “q

” and has at least 2 charactersLIKE 'q

__%'Find values ​​starting with “q

” and be at least 3 characters longLIKE 'q%q

'Find values ​​starting with “q” and ends with “q

In the above examples, it can be replaced qtm and q

with any characters you see fit.

Example of LIKE in SQL

SQL Like with multiple values

SELECT *
FROM Customers
WHERE last_name LIKE 'R%t' OR last_name LIKE '%e';

You can use the LIKE operator with multiple string patterns to select rows by using the OR operator. For example:

This SQL command selects customers whose last_name begins with the letter R and ends with t, or customers whose last_name ends with e.

SQL NOT LIKE operator

SELECT *
FROM Customers
WHERE country NOT LIKE 'USA';

You can also reverse the behavior of the LIKE operator and ignore the group of results that match the given string pattern by using the NOT operator. For example:

Here, the SQL command selects all customers, except in the US.

SQL Like with wildcards

SELECT *
FROM Customers
WHERE country LIKE 'U_';

You can now use more wildcards. Let's look at another example. Here we use the wildcard _ with LIKE in SQL.

This SQL command selects customers in countries whose names begin with U and are followed by 1 character.

+----+----------+-----+-----------+----------+
| ID | TEN      |TUOI | DIACHI    | LUONG    |
+----+----------+-----+-----------+----------+
|  1 | Thanh    |  32 | Haiphong  |  2000.00 |
|  2 | Loan     |  25 | Hanoi     |  1500.00 |
|  3 | Nga      |  23 | Hanam     |  2000.00 |
|  4 | Manh     |  25 | Hue       |  6500.00 |
|  5 | Huy      |  27 | Hatinh    |  8500.00 |
|  6 | Cao      |  22 | HCM       |  4500.00 |
|  7 | Lam      |  24 | Hanoi     | 10000.00 |
+----+----------+-----+-----------+----------+

Let's take a real example, consider the NHANVIEN table with records as below.

SELECT * FROM NHANVIEN
WHERE LUONG LIKE '200%';

For example, to display all records from the NHANVIEN table whose LUONG starts with 200, we do the following:

+----+----------+-----+-----------+----------+
| ID | TEN      |TUOI | DIACHI    | LUONG    |
+----+----------+-----+-----------+----------+
|  1 | Thanh    |  32 | Haiphong  |  2000.00 |
|  3 | Nga      |  23 | Hanam     |  2000.00 |
+----+----------+-----+-----------+----------+

The returned results displayed are:

SELECT * FROM NHANVIEN
WHERE LUONG LIKE 'H%M';

Next, we need to see the list of people with the name DIACHI starting with the letter H and ending with the letter M:

+----+----------+-----+-----------+----------+
| ID | TEN      |TUOI | DIACHI    | LUONG    |
+----+----------+-----+-----------+----------+
|  3 | Nga      |  23 | Hanam     |  2000.00 |
|  6 | Cao      |  22 | HCM       |  4500.00 |
+----+----------+-----+-----------+----------+

The resulting table will be as follows:

Use the Escape character with the Like SQL operator

The SQL escape character is used to remove certain wildcard characters from the expression of the LIKE operator. This way, you can use those wildcards as usual.

Using escape, you can also avoid using characters reserved in SQL syntax to represent certain scripts, such as apostrophes “ ' , “%” and “_”.

For example, if you need to search for % as a character string in a LIKE condition, you need the Escape character. Note

– An escape character is only specified as a single character.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE 'pattern ESCAPE escape_character';

The syntax for using the LIKE operator with the escape character is as follows:

  • In the box: pattern. pattern
  • is the pattern you want to combine ESCAPE
  • is a keyword indicating escape characters. escape_character

is the character you want to use as an escape character.

For example:

CREATE TABLE EMPLOYEE (
   SALARY DECIMAL (18,2) NOT NULL,
   BONUS_PERCENT VARCHAR (20)
);

Create a new table EMPLOYEE using below query – :

INSERT INTO EMPLOYEE VALUES (67000.00, '45.00');  
INSERT INTO EMPLOYEE VALUES (54000.00, '20.34%');     
INSERT INTO EMPLOYEE VALUES (75000.00, '51.00');         
INSERT INTO EMPLOYEE VALUES (84000.00, '56.82%');

Now you can insert values ​​into these empty tables using the INSERT command as follows:

The Employee table includes the salary of employees in the company and the bonus percentage in the salary as shown below:
+———-+————–+
| SALARY | BONUS_PERCENT |
+———-+————–+
| 67000.00 | 45.00 |
| 54000.00 | 20.34% |
| 75000.00 | 51.00 |
| 84000.00 | 56.82% |

+———-+————–+

select * from CUSTOMERS WHERE BONUS_PERCENT LIKE'%!%%' escape '!';

Now try displaying all data records in the EMPLOYEE table, where BONUS_PERCENT contains the character %.

Result:
+———-+————–+
| SALARY | BONUS_PERCENT |
+———-+————–+
| 54000.00 | 20.34% |
| 84000.00 | 56.82% |

+———-+————–+

Importance of SQL LIKE

One of the important things to note about the LIKE operator in SQL is that by default it is case-insensitive in most database systems. That means if you search for the keyword apple using the LIKE operator, it will return results including “Apple”, “APPLE”, “aPpLe”…

To make the LIKE operator case-sensitive, you can use the “BINARY” keyword in MySQL or the “COLLATE” keyword in other database systems.

SELECT * FROM products WHERE name LIKE BINARY 'apple%'

For example:

The following query will only return products whose names start with “apple” and are spelled exactly that way, without capital letters.

In the next part, we will learn about the TOP command, please remember to follow along.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments