Yogesh Chauhan's Blog

Some SQL LIKE Operators We Need to Keep in Mind

in SQL/MySQL on August 24, 2019

In this short blog post I am going to discuss about how to use 5 LIKE operator in SQL WHERE clause. 

We use LIKE operator in a WHERE clause to find a specified pattern in a column.

% and _ are two wildcards which often used with the LIKE operator:

  • % (Percentage) – represents zero, one, or more characters
  • _ (Underscore) – represents a single character

In this blog I'll talk about % and in the next blog, I'll cover the _ examples. Let's take a look at the syntax first.


SELECT column1, column2, column3 ...
FROM table
WHERE column LIKE pattern;
  • LIKE 'c%'

The LIKE 'c%' checks for any values that start with "c". After "c" there might be one, two or more characters in the value.

SQL Query:


SELECT * FROM country WHERE Name LIKE 'c%' LIMIT 10;

The SQL query will find all the country names starting with "c".

  • LIKE '%c'

The LIKE '%c' checks for any values that end with "c". Before "c" there might be one, two or more characters in the value.

SQL Query:


SELECT * FROM country WHERE Name LIKE '%c';

The query above will find all the country names ending with "c".

  • LIKE '%ca%'

The LIKE '%ca%' is going to check for any values which contains "ca" in any position. Before and after that "ca" can be multiple characters in place.

SQL Query:


SELECT * FROM country WHERE Name LIKE '%ca%';

The SQL query above will find all the country names which has "ca" in their names.

  • LIKE '_c%'

The LIKE '_c%' finds any values that have "c" in the second position. The first letter / character can be anything.

SQL Query:


SELECT * FROM country WHERE Name LIKE '_c%';

The above query will show countries with "c" in the second position in their names.

  • LIKE 'c_%'

The LIKE 'c_%' finds any values that have "c" in the first position and there are at least 3 characters in the values length.

SQL Query:


SELECT * FROM country WHERE Name LIKE 'c_%';

The query above will show the country names starting with "c" and have at least 3 characters in their names.

  • LIKE 'c%o'

The LIKE 'c%o' finds any values that start with "c" and end with "o". The middle character can be anything.

SQL Query:


SELECT * FROM country WHERE Name LIKE 'c%o';

The query above shows the country names starting with "c" and ending with "o" with any 1 or more characters in the middle.

  • NOT LIKE 'a%

The NOT LIKE 'a% finds values that doesn't start with "a"

SQL Query:


SELECT * FROM country WHERE Name NOT LIKE 'a%';

I have just added NOT in the simple first query. So it will display country names which doesn't start with an "a".


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to calculate elapsed time in JavaScript?JavaScript5 Key Principles Of Good Website UsabilityUI/UXHow to get start index and end index (as int) of substring in Swift?Swiftfirst-of-type and last-of-type selectors in CSSCSStransform-origin Property in CSSCSSHow to Commit and Rollback Changes in SQL?SQL/MySQL