SQL LIKE OPERATOR

SQL LIKE operator is used to search for a specifid pattern in a column with the WHERE condition clause.

Types of LIKE Operator

Two wildcards are used to along with the LIKE operator to retrieve data.

  • % [Percent] - Sign represents ZERO or Multiple characters.
  • _ [Underscore] - Sign represent a single character.

LIKE Operator Syntax

SELECT  column_1, column_2, column_3, ....
FROM table_name
WHERE column_name LIKE pattern;

LIKE Operator Pattern

Patterns with Operator Description
Where column_name LIKE 'b%' Finds any values that START with "b" in that column
Where column_name LIKE '%b' Finds any values that END with "b" in that column
Where column_name LIKE '%b%' Finds any values that have "b" in any position
Where column_name LIKE '_a%' Finds any values that have "a" in any seccond position
Where column_name LIKE 'b__%' Finds any values that START with "b" and at least 3 characters in lenght
Where column_name LIKE 'b%c' Finds any values that START with "b" and END with "c"

Student Table

A table of student records, the name of table is "Student", and the name of columns are [ROLL_NO, STUDENT_NAME, ADDRESS, MOBILE_NO, AGE, and BRANCH]:

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
1 Mohit Sharma Delhi XXXXXXXX87 20 IT
2 Pooja Pandit Kolkata XXXXXXXX34 18 CSE
3 Priya Pandey Delhi XXXXXXXX62 19 EEE
4 Mohit Kumar Jha Allahabad U.P XXXXXXXX87 20 ECE
5 Shevani Shree Noida U.P XXXXXXXX51 17 IT
6 Badshah Mumbai XXXXXXXX02 20 Civil
7 Viki Mohan Pune XXXXXXXX72 19 CSE
8 Sona Kaur Indor M.P XXXXXXXX81 22 ME

Example of LIKE Operator 'b%'

SELECT  *
FROM Student
WHERE STUDENT_NAME LIKE 'p%';

Result Look Like

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
2 Pooja Pandit Kolkata XXXXXXXX34 18 CSE
3 Priya Pandey Delhi XXXXXXXX62 19 EEE

Example of LIKE Operator '%a'

SELECT  *
FROM Student
WHERE STUDENT_NAME LIKE '%a';

Result Look Like

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
1 Mohit Sharma Delhi XXXXXXXX87 20 IT
4 Mohit Kumar Jha Allahabad U.P XXXXXXXX87 20 ECE

Example of LIKE Operator '_o%'

SELECT  *
FROM Student
WHERE STUDENT_NAME LIKE '_o%';

Result Look Like

ROLL_NO STUDENT_NAME ADDRESS MOBILE_NO AGE BRANCH
1 Mohit Sharma Delhi XXXXXXXX87 20 IT
2 Pooja Pandit Kolkata XXXXXXXX34 18 CSE
4 Mohit Kumar Jha Allahabad U.P XXXXXXXX87 20 ECE
8 Sona Kaur Indor M.P XXXXXXXX81 22 ME

Policy

GDATAMART.COM is optimized for learning & job information; The users try to improve your knowledge and practical experience through this site but we cannot warrant full correctness of all content... Learn More → | Sitemap →

© 2020 GDATAMART | All Rights Reserved