Home / mySQL / SQL LIKE Operator

SQL LIKE Operator

Good morning to all,
Welcome to SQL commnands, queries, operators etc. Now we are discuss the SQL LIKE operator. The LIKE operators used to fetch the data from database foor specific pattern.
The LIKE operator is used to search for a specified pattern in a column used in WHERE clause. There are two wildcards used in conjunction with the LIKE operator:

  • The percent sign (%)- represents zero, one or more than one character.
  • The underscore (_)-represents only one character

SQL LIKE SYNTAX

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

The Basic Syntax of % and _ is as follows:

SELECT FROM table_name
WHERE column LIKE ‘YYYY%’

or

SELECT FROM table_name
WHERE column LIKE ‘%YYYY’

or

SELECT FROM table_name
WHERE column LIKE ‘%YYYY%’

or

SELECT FROM table_name
WHERE column LIKE ‘YYYY_’

or

SELECT FROM table_name
WHERE column LIKE ‘_YYYY’

or

SELECT FROM table_name
WHERE column LIKE ‘_YYYY_’

You can combine N number of conditions using AND or OR operators. Here, YYYY could be any numeric or string value.

Here are number of examples showing WHERE part having different LIKE clause with ‘%’ and ‘_’ operators

Statement Description
WHERE MARK LIKE ‘500%’ Finds any values that start with 500
WHERE MARK LIKE ‘%500%’ Finds any values that have 500 in any position
WHERE MARK LIKE ‘_00%’ Finds any values that have 00 in the second and third positions
WHERE MARK LIKE ‘5_%_%’ Finds any values that start with 5 and are at least 3 characters in length
WHERE MARK LIKE ‘%5’ Finds any values that end with 5
WHERE MARK LIKE ‘_2%3’ Finds any values that have a 2 in the second position and end with a 3
WHERE MARK LIKE ‘2___3’ Finds any values in a five-digit number that start with 2 and end with 3

Example of LIKE clause

Consider Employee table

Employee_ID Name Salary
101 Adam 15000
102 Alex 18000
103 Abhi 17000

Example 1: SELECT * from Employee where Name like ‘A%’;

The above query will return all records where Name starts with character ‘A’ from Employee table.

Employee_ID Name Salary
101 Adam 15000
102 Alex 18000
103 Abhi 17000

Example2: SELECT * from Employee where Name like ‘_d%’;

The above query will return all records from Employee table where Name contain ‘d’ as second character.

Employee_ID Name Salary
101 Adam 15000

Example 3: SELECT * from Employee where Name like ‘%x’;

The above query will return all records from Employee table where Name contain ‘x’ as last character.

Employee_ID Name Salary
102 Alex 18000

Example4: SELECT * from Employee where Name like ‘%d%’;

The above query will return all records from Employee table where Name contain ‘d’ as second character.

Employee_ID Name Salary
101 Adam 15000

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Leave a Reply

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

Social Media Auto Publish Powered By : XYZScripts.com