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 tim@myemail.uk Active
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk Inactive
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active
6 Rita Johns +44 55 453-4534 London rita@myemail.uk 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 richard@myemail.uk Active
6 Rita Johns +44 55 453-4534 London rita@myemail.uk 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 james@myemail.uk Active
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
6 Rita Johns +44 55 453-4534 London rita@myemail.uk 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

Search

Shortcodes Ultimate

 
Follow Us
 
BGP