LIKE Operator in SQL

LIKE Clause

LIKE operator, in SQL, is a wildcard operator used to compare a value with similar values. There are two wildcards utilized as a part of conjunction with the LIKE operator:

  • % Percentile
  • _ Underscore

The percent sign refers to zero, one, or different characters whereas the underscore refers to a solitary/single number or character. These wildcards can be utilized separately or in conjunction.

Syntax

SELECT col_name_1, col_name_2, … , col_name_n
FROM tbl_name
WHERE col_name LIKE pattern;

In the above syntax, pattern can either be ‘%XXXXX’ or “XXXXX%”, or “%XXXXX%” or even

“XXXX_”, or “_XXXX”, or “_XXXX_”

For example

The current table view of the Customers table is as follows:

ID C_Name Contact_No City Email Status
1 Tim Robbins +44 54 443-4434 London [email protected] Active
2 James Chris +44 54 498-3476 Birmingham [email protected] Active
3 Kevin Sputnik +44 54 487-6987 Manchester [email protected] Inactive
4 Richard Butler +44 54 422-2345 Birmingham [email protected] Active
5 David McGregor +44 54 413-0989 Cardiff [email protected] Active
6 Rita Johns +44 55 453-4534 London [email protected] Active

SELECT *
FROM Customers
WHERE C_Name LIKE ‘ri%’;

The output of this query is as follows

ID C_Name Contact_No City Email Status
4 Richard Butler +44 54 422-2345 Birmingham [email protected] Active
6 Rita Johns +44 55 453-4534 London [email protected] Active

The above two records have “Ri” in the start of the name.

In another example:

SELECT *
FROM Customers
WHERE C_Name LIKE ‘%ri%’;

ID C_Name Contact_No City Email Status
2 James Chris +44 54 498-3476 Birmingham [email protected] Active
4 Richard Butler +44 54 422-2345 Birmingham [email protected] Active
6 Rita Johns +44 55 453-4534 London [email protected] Active

In above results, the system searched for the occurrence of “RI” in C_Name column and returned all the records where RI occurred in combine manner, regardless of their position within the name.

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes