T-SQL LIKE Operator
In Transact-SQL, The LIKE operator is used to search the pattern specified in the column with WHERE clause. The LIKE clause in Transact-SQL is used to compare the same values to use the wildcard operators.
Two wildcards are used in the combination with LIKE operator:
- % – The percent sign represents zero, one or some characters.
- _ – The underscore sign represents a single character.
Note: MS Access uses the asterisk (*) sign in place of a percent sign (%) and a question mark (?) Instead of underscore (_).
The percent sign represents ‘0’, ‘1’ or more characters in LIKE operator. And the underscore sign represents one character.
Syntax:
Basic syntax of % (Percent Sign) and _ (the underscore) according to the LIKE operator.
| No. | Queries |
|---|---|
| 1 | SELECT *column-list FROM table_name WHERE column LIKE ‘XXXX%.’ |
| 2 | SELECT *column-list FROM table_name WHERE column LIKE ‘_XXXX.’ |
| 3 | SELECT *column-list FROM table_name WHERE column LIKE ‘_XXXX_.’ |
| 4 | SELECT *column-list FROM table_name WHERE column LIKE ‘XXXX_.’ |
| 5 | SELECT *column-list FROM table_name WHERE column LIKE ‘%XXXX%.’ |
We bind the conditions of LIKE operator by using the AND or OR operators. “XXXX” is a numeric or string value.
Example:
Below are some examples in which we are showing WHERE part having the different LIKE clause with the modules ‘%’ and ‘_’ operators.
| Sr.No | Statement & Description |
|---|---|
| 1 | WHERE SALARY LIKE ‘300%.’ It finds any value that starts with 300 |
| 2 | WHERE SALARY LIKE ‘%300%.’ It findsany value that has 300 in any position |
| 3 | WHERE SALARY LIKE ‘_000%.’ It finds any value that has 000 in the second, third and fourth positions |
| 4 | WHERE SALARY LIKE ‘3_%_%.’ It finds any value that starts with 3 and have at least 3 characters in length |
| 5 | WHERE SALARY LIKE ‘%4’ It finds any value that ends with 4 |
| 6 | WHERE SALARY LIKE ‘_3%4.’ It finds any value that has a 3 in the second position and ends with 4 |
| 7 | WHERE SALARY LIKE ‘3___4’ It finds any value in a five-digit number that starts with 3 and ends with 4 |
Consider the CUSTOMERS table having the following records.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 01 | William | 32 | Karachi | 7000.00 |
| 02 | Avery | 24 | London | 3000.00 |
| 03 | Jackson | 34 | Paris | 1200.00 |
| 04 | Harper | 20 | New York | 1500.00 |
| 05 | Ella | 22 | Islamabad | 3400.00 |
| 06 | Monty | 23 | Turkey | 4400.00 |
| 07 | Mason | 26 | Saudi Arabia | 5050.00 |
Example 1:
The command is an example, which will display all the records from the CUSTOMERS table where SALARY starts with 200.
The above command will produce the below output.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 01 | William | 32 | Karachi | 7000.00 |
Example 2:
Below command is the example, which displays all the records from the CUSTOMERS table where SALARY ends with 50.
The command gives the below output.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 07 | Mason | 26 | Saudi Arabia | 5050.00 |
Example 3:
The given command is an example, which can display all the records from the CUSTOMERS table where SALARY starts with 1 and ends with 0.
The above command gives the below output.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 04 | Harper | 20 | New York | 1500.00 |