BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

LIKE Clause in SQL

By Chaitanya Singh | Filed Under: SQL

Like clause is used for fetching similar values from table(s). For e.g. you may want to fetch all the names from a table that starts with alphabet “A” and ends with alphabet “X”, in such case you can use like clause in SQL query. In this tutorial we will see variations and use of Like clause in SQL queries.

Syntax:

SQL> SELECT column_name1, column_name2....
FROM TableName
WHERE [condition] 
LIKE Expression;

Expression can contain percentage sign(%) or underscore(_) or both.
Here percentage sign(%) represents a string of character having zero or more characters and underscore(_) refers a single character. It may sound confusing, lets take few examples to understand them better:

Lets say I have a table “STUDENT” with a field(column) called STUDENT_NAME.

1) Find all the student names that start with character “A”:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE 'A%';

2) Find all the student names that start with character “X” and end with char “Z”:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE 'A%Z';

3) Find student names having 2nd and 4th character as “C” and “A” respectively:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_C_A%';

4) Find student names that end with char “B” and have only 4 characters in name:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_ _ _B';

5) Find student names that have a “E” at the second position and end with “Y”:

SQL> SELECT STUDENT_NAME FROM STUDENT
WHERE STUDENT_NAME LIKE '_E%Y';

Complete Example

STUDENT table:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10001  | Kate     |  22 | CSE       |  Mumbai  |
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10003  | Rick     |  33 | ME        |  Chennai |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
|  10005  | Steve    |  22 | CSE       |  Noida   |
|  10006  | Mark     |  22 | ECE       |  Jaipur  |
|  10007  | Brad     |  23 | ME        |  Rampur  |
+---------+----------+-----+-----------+----------+

Find all the records from the above table that have CITY names starting with char “D”.

SQL> SELECT NAME FROM STUDENT
WHERE CITY LIKE 'D%';

Result:

+---------+----------+-----+-----------+----------+
| ROLL_NO | NAME     | AGE | BRANCH    | CITY     |
+---------+----------+-----+-----------+----------+
|  10002  | Richard  |  21 | ECE       |  Delhi   |
|  10004  | Peter    |  26 | CSE       |  Delhi   |
+---------+----------+-----+-----------+----------+

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Copyright © 2012 – 2022 BeginnersBook . Privacy Policy . Sitemap