Which wildcards symbol is used in SQL for the pattern matching in the select statement?

We have already discussed about the SQL LIKE operator, which is used to compare a value to similar values using the wildcard operators.

SQL supports two wildcard operators in conjunction with the LIKE operator which are explained in detail in the following table.

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

Syntax

The basic syntax of a '%' and a '_' operator 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 the AND or the OR operators. Here, XXXX could be any numeric or string value.

Example

The following table has a number of examples showing the WHERE part having different LIKE clauses with '%' and '_' operators.

Let us take a real example, consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block is an example, which would display all the records from the CUSTOMERS table where the SALARY starts with 200.

SQL> SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '200%';

This would produce the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

SQL LIKE query Command

Using Like Query with wildcard in different combinations, we can match our keyword with the pattern of the data present in columns.

The best way to use LIKE command is to apply it against a text or varchar field along with wildcard % or _

• Video Tutorial on LIKE Query with AND , OR NOT combinations.

Here is our table with all the records.

SELECT * FROM student WHERE name LIKE '%John%'

Read how Regular expression is used to Pattern matching

SELECT * FROM student WHERE name LIKE 'John%'

SELECT * FROM student WHERE name LIKE '%John'

Matching string in name column starting with A and ending with n.

SELECT * FROM student WHERE name LIKE 'A%n'

6	Alex John	Four	55	male

Use of underscore ( _) as wildcard in string matching

SELECT * FROM account_master WHERE acc_no LIKE '__044'

Underscores as wildcard can be used at any location but one can replace one character only. We can use more than one underscore also inside our query.

Using NOT with LIKE

SELECT * FROM student WHERE name NOT LIKE '%John%'

Case Sensitive query

select * from student where name LIKE binary '%A'

Name of the students having letter 'a' and letter 'e' ( without single quotes )

SELECT * FROM student WHERE name LIKE  '%a%' AND name LIKE  '%e%'

SELECT * FROM student WHERE name LIKE  'C%' AND name NOT LIKE  '_h%'

Searching keyword across multiple columns by using AND , OR

SELECT * FROM TABLE_NAME 
WHERE Column1 Like '%keyword%' AND Column2 LIKE '%keyword%'

SELECT * FROM TABLE_NAME 
WHERE Column1 Like '%keyword%' OR Column2 LIKE '%keyword%'

LIKE with CONCAT

SELECT * FROM student WHERE 
' Our best student is Mr  John Deo  of 5th Class'   
LIKE  CONCAT('%',name,'%')

CONCAT to join strings in Query

Limitation of Like query

Displaying records using PHP Script

Here is the sample code.

<?Php
require "config.php";

$count="SELECT * FROM student WHERE name LIKE '%John%'";

echo "<table>";
echo "<tr><th>id</th><th>name</th><th>class</th><th>mark</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";
?>

▼ More on getting records from table with different combinations of commands

Which is used for pattern matching in SQL?

Which one is a wildcard used for pattern matching?

What are the wildcards symbol used in SQL?