Some SQL LIKE Operators We Need to Keep in Mind

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

Share this post