YogeshChauhan . com

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".

amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

What are “holes” in a React component?ReactWordPress: How to get ACF field values from another post?WordPressJavaScript String Properties and MethodsJavaScriptHow to create a dynamic countdown using HTML and JavaScript?HTMLHow to scroll contents of a an element vertically using JavaScript?JavaScriptHow to Remove PHP File Extensions From Your Website URLs?PHP