beginnersbook.com

  • Home
  • All Tutorials
    • Learn Servlet
    • Learn JSP
    • Learn JSTL
    • Learn C
    • Learn C++
    • Learn MongoDB
    • Learn XML
    • Learn Python
    • Learn Perl
    • Learn Kotlin
  • Core Java
  • OOPs
  • Collections
  • Java I/O
  • JSON
  • DBMS

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   |
+---------+----------+-----+-----------+----------+

Enjoyed this post? Try these related posts

  1. UNIQUE Constraint in SQL
  2. SQL SELECT TOP Statement
  3. DEFAULT Constraint in SQL
  4. NOT NULL Constraint in SQL
  5. SQL Syntax
  6. SQL AND, OR and NOT Operators with examples

Leave a Reply Cancel reply

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

SQL Tutorial

  • SQL Introduction
  • SQL Syntax
  • 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

Recently Added..

  • JSON Tutorial
  • Java Regular Expressions Tutorial
  • Java Enum Tutorial
  • Java Annotations Tutorial

Copyright © 2012 – 2021 BeginnersBook . Privacy Policy . Sitemap