LIKE Operator in SQL

SQL LIKE operator serves for filtering rows. It uses a pattern in a column. This operator is typically paired with WHERE clause. It's used in SELECT statement.

Here is the basic structure of SQL LIKE operator:

SELECT column1, column2, ... FROM yourtable WHERE column LIKE pattern;

Let's take a look at the usage of LIKE operator with varied patterns.

Wildcards are employed with LIKE operator. "%" is used to represent one or multiple characters. This character can be at the beginning, middle or end of a value. For instance, consider "man%" pattern. It would match words like "mankind" or "manager". Similarly consider the pattern "%man" which would match words like "superman" or "postman".

"_" is another wildcard character. It represents just one character. This character can be at any position in a string. Thus, consider "f_sn" pattern. It will match the word "fusion" or "fascination".

SQL LIKE operator is a very handy tool. It is used to filter rows based on a pattern in a specified column. Mostly used with WHERE clause in SELECT statement.

The basic syntax of LIKE operator is pretty simple. It is:

SELECT column1, column2, ... FROM table WHERE column LIKE pattern;

How to use LIKE operator with different patterns? There are some examples. Wildcard % is an example. It represents zero, one or multiple characters. For example WHERE column LIKE 'John%' will match any value beginning with "John".

Let's see an example of underscore _. Underscore represents a single character. Take this example: WHERE column LIKE 'J_ne' will match "Jane", "Jone", etc.

Next is square brackets []. These square brackets match any one of the characters within them. For instance, consider WHERE column LIKE 'J[aeiou]hn'. This will match "John", "Jahn", "Juhn" etc.

Hyphen - in Square Brackets:

Indicates range of characters in brackets. This is for example: WHERE column LIKE 'J[a-z]hn' will match "John", "Jahn", "Jbhn" etc. Here are some useful examples:

• Selecting names that begin with "A": SELECT * FROM users WHERE name LIKE 'A%';
• Finding names that end with "son": SELECT * FROM users WHERE name LIKE '%son';
• Find names that have the string "an" within them: SELECT * FROM users WHERE name LIKE '%an%';
• Find the names where the second character is "a": SELECT * FROM users WHERE name LIKE '_a%';

Remember LIKE operator is by default case-sensitive. If you want a search that is case-insensitive, you might need functions. Functions like LOWER() and UPPER() make the data consistent in case before applying LIKE operator. Here is an example:

SELECT * FROM users WHERE LOWER(name) LIKE 'john%';