What is the LIKE operator in MySQL
LIKE operator in MySQL is used in the WHERE condition to get certain values of column data.
We must give the pattern in the LIKE condition to get the data. We have two signs we can use in like condition.
- % (percentage)
- _ (Underscore)
The syntax for the LIKE condition
SELECT column_name1, column_name2, …
FROM schema.table_name
WHERE column_name LIKE pattern;
We will see some examples using the above 2 signs and will see how the condition works. We will use the same ‘students’ table created in MySQL SELECT Statement.
- % using before chars
- % after chars
- % before and after chars
- % in between the chars
- _ before char and %
- _ after char and %
- _ (to get a particular length of string values)
- % using before chars
Query:
select * from students where name like ‘%S’;
- This will give the records for which name ends with S. Since we have added % before S.
- It will match any string that ends with S irrespective of which and how many characters are present in that string before S.
- We can also add multiple characters after % signs.
- For example, if we want to get the string ends with ‘MS’ then we can add a condition like ‘%MS’
- % after chars
Query:
select * from students where name like ‘K%’;
- This will give the records for which the name starts with K. Since we have added % after K.
- It will match any string that starts with K irrespective of which and how many characters are present in that string after K.
- We can also add multiple characters before % signs.
- For example, if we want to get the string starts with ‘KJ’ then we can add a condition like ‘KJ%’
- % before and after chars
Query:
select * from students where name like ‘%K%’;
- This will get the records of the name which has K in it. Irrespective of the position of the K. It can be at the start, end, or in the middle.
- % in between the chars
Query:
select * from students where name like ‘K%Y’;
- This will get the records of the names which start with K and ends with Y
- _ before char and %
Query:
select * from students where name like ‘_K%’;
- This will get the record for a name that has K in the second position.
- We can add multiple _ to define the position of the K. if we want to get the name which has K at the 3rd position we can add two underscore signs before K, like ‘__K%’;
Query: (with 2 underscores before the character)
select * from students where name like ‘__K%’;
- _ after char and %
Query:
select * from students where name like ‘%K_’;
- This will get the records for the name which has K at the second last position.
- _ (to get a particular length of string values)
Query:
select * from students where name like ‘_____’;
- Since in the above query we have added 5 underscores we will get the result for which name has 5 characters in it.