Posted By
Bajarangi soft ,
Posted On 18-09-2020
The SQL LIKE Operator
The LIKE operator is used in a WHERE clause to find specified values in a column.
There are two wildcards in LIKE operator:
Syntax for SQL LIKE Operator
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
We have some of LIKE Operator to use:
LIKE Operator Description
WHERE clause LIKE 'a%' search any values that start with "a"
WHERE clause LIKE '%a' search any values that end with "a"
WHERE clause LIKE '%or%' search any values that have "or" in any position
WHERE clause LIKE '_r%' search any values that have "r" in the second position
WHERE clause LIKE 'a_%' search any values that start with "a" and are at least 2 characters in length
WHERE clause LIKE 'a__%' search any values that start with "a" and are at least 3 characters in length
WHERE clause LIKE 'a%o' search any values that start with "a" and ends with "o"
Lets learn
1.Create database and table
Student table.
Here we have student table to perform SQL LIKE Operator
| StudentID | StudentName | Address | DOB | Section |
Branch |
Fee |
1 |
Liam | 23 MG road Banglore |
12/06 | A | CSE | 66,000 |
| 2 | Andrew | 2415 Anthony market str | 01/02 | A | ISE | 65,000 |
| 4 | Ryan | 18 Krishna sq. Banglore |
26/12 | B | EE | 70,000 |
| 5 | Charles | 878 Eli Berguvsvägen 8 | 23/11 | C | ECE | 75,000 |
| 8 | Alexander | 23 Andrew road | 12/06 | A | CSE | 66000 |
| 9 | Daniel | 2415 Anthony market str | 01/02 | A | ISE | 65000 |
| 10 | Jackson | 18 Christopher sq. | 26/12 | B | EE | 70000 |
| 11 | Connor | 878 Eli Berguvsvägen 8 | 23/11 | C | ECE | 75000 |
SELECT * FROM Student WHERE StudentName LIKE 'a%'
Example(2) for SQL LIKE Operator to search any values that end with "a".
SELECT * FROM Student WHERE StudentName LIKE '%a'
SELECT * FROM Student WHERE StudentName LIKE '%or%'
SELECT * FROM Student WHERE StudentName LIKE '_r%'
Example(5) for SQL LIKE Operator to search any values that start with "a" and are at least 2 characters in length
SELECT * FROM Student WHERE StudentName LIKE 'a__%
SELECT * FROM Student WHERE StudentName LIKE 'a%o'
SELECT * FROM Student WHERE StudentName LIKE 'a%'
<!DOCTYPE html>
<html lang="en">
<head>
<title>SQL LIKE Operator</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<br>
<br>
<br>
<div class="text-center">
<h1>SQL LIKE Operator</h1>
</div>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "school_management");
if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}
$sql_1 = "SELECT * FROM Student WHERE StudentName LIKE 'a%';";//search any values that start with "a"
$sql_2= "SELECT * FROM Student WHERE StudentName LIKE '%a';";//search any values that end with "a"
$sql_3= "SELECT * FROM Student WHERE StudentName LIKE '%or%'";//search any values that have "or" in any position
$sql_4= "SELECT * FROM Student WHERE StudentName LIKE '_r%'";//search any values that have "r" in the second position
$sql_5= "SELECT * FROM Student WHERE StudentName LIKE 'a__%'";//search any values that start with "a" and are at least 2 characters in length
$sql_6= "SELECT * FROM Student WHERE StudentName LIKE 'a%o'";//search any values that start with "a" and are at least 3 characters in length
$sql_7= "SELECT * FROM Student WHERE StudentName LIKE 'a%'";//search any values that start with "a" and ends with "o"
echo "<h2 class='text-danger'>Search any values that start with a</h2>";
if($result_1 = mysqli_query($connect, $sql_1)){
if(mysqli_num_rows($result_1) > 0){
echo "<table class='table'>
<tr>
<th>StudentID</th>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
<th>Section</th>
<th>Branch</th>
<th>Fee</th>
</tr>
";
while($row = $result_1->fetch_assoc()){
echo "<tr>
<td>" . $row['StudentID'] . "</td>
<td>".$row['StudentName'] ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
<td>".$row['Section'] ."</td>
<td>".$row['Branch'] ."</td>
<td>".$row['Fee'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
echo"<br>";
echo "<h2 class='text-danger'>Search any values that end with a</h2>";
if($result_2 = mysqli_query($connect, $sql_2)){
if(mysqli_num_rows($result_2) > 0){
echo "<table class='table'>
<tr>
<th>StudentID</th>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
<th>Section</th>
<th>Branch</th>
<th>Fee</th>
</tr>
";
while($row = $result_2->fetch_assoc()){
echo "<tr>
<td>" . $row['StudentID'] . "</td>
<td>".$row['StudentName'] ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
<td>".$row['Section'] ."</td>
<td>".$row['Branch'] ."</td>
<td>".$row['Fee'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
echo"<br>";
echo "<h2 class='text-danger'>Search any values that have or in any position</h2>";
if($result_3 = mysqli_query($connect, $sql_3)){
if(mysqli_num_rows($result_3) > 0){
echo "<table class='table'>
<tr>
<th>StudentID</th>
<th>StudentName</th>
<th>Address</th>
<th>DOB</th>
<th>Section</th>
<th>Branch</th>
<th>Fee</th>
</tr>
";
while($row = $result_3->fetch_assoc()){
echo "<tr>
<td>" . $row['StudentID'] . "</td>
<td>".$row['StudentName'] ."</td>
<td>".$row['Address'] ."</td>
<td>".$row['DOB'] ."</td>
<td>".$row['Section'] ."</td>
<td>".$row['Branch'] ."</td>
<td>".$row['Fee'] ."</td>
</tr>";
}
echo "</table>";
} else{
echo "No records found.";
}
}
?>
</div>
<br>
</div>
</body>
</html>