$Applaud$

SQL LIKE Clause - Orapoint

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:

  • The percent sign (%)
  • The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Syntax: The basic syntax of % and _ is as follows:

SELECT FROM table_name WHERE column LIKE 'XXXX%'; 
(or) 
SELECT FROM table_name WHERE column LIKE '%XXXX%'; 
(or) 
SELECT FROM table_name WHERE column LIKE 'XXXX_'; 
(or) 
SELECT FROM table_name WHERE column LIKE 'XXXX'; 
(or) 
SELECT FROM table_name WHERE column LIKE '_XXXX';

You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

Example: Here are number of examples showing WHERE part having different LIKE clause with ‘%’ and ‘_’ operators:

Statement Description
WHERE SALARY LIKE ‘200%’ Finds any values that start with 200
WHERE SALARY LIKE ‘%200%’ Finds any values that have 200 in any position
WHERE SALARY LIKE ‘_00%’ Finds any values that have 00 in the second and third positions
WHERE SALARY LIKE ‘2_%_%’ Finds any values that start with 2 and are at least 3 characters in length
WHERE SALARY LIKE ‘%2’ Finds any values that end with 2
WHERE SALARY LIKE ‘_2%3’ Finds any values that have a 2 in the second position and end with a 3
WHERE SALARY LIKE ‘2___3’ Finds any values in a five-digit number that start with 2 and end with 3

Let us take a real example, consider the DEPT table having the following records:

deptno dname location
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston

Following is an example, which would display all the records from DEPT table where DNAME starts with A:

SELECT * FROM DEPT WHERE DNAME LIKE 'A%';

This would produce the following result:

deptno dname location
10 Accounting New York