MYSQL - “LIKE operator”

The LIKE operator is used in a WHERE clause to search for a pattern in a column.

Two wildcards are often used while using the LIKE operator:

1. Percentage sign( % ) — it represents multiple characters ,zero ,one.
2. Underscore sign( _ ) — it represents single character.

Create database before creating a table.

commands : CREATE DATABASE pannts_db, USE pants_db

creating pants_db table and inserting values

LIKE ‘J%’ — finds any value that starts with “J”

In the below syntax it shows the values from “Article column” where the value starts with “J” . LIKE ‘J%’. It shows only those values as per the condition described.

LIKE ’J%’

LIKE ‘%e’ — finds any values that ends with “e”

In the below syntax it shows the values from “color column” where the value ends with the letter ”e”. LIKE ‘%e’. It shows only those rows as per the condition described.

LIKE ‘%e’

LIKE ‘%a%’ — finds any values that have or in any position.

In the below syntax it shows values from “Article column” where the letter “a” is available in any position based on the condition specified.

LIKE ‘%a%’

LIKE ‘_e%’ — finds any value that have “e” in the 2nd position.

In the below syntax it only shows where the letter “e” is in 2nd position from “color column”.

LIKE ‘_e%’

Another example : LIKE ‘_l%’

LIKE ‘B_%’ — finds any values that starts with “B” and are atleast 2 characters in length.

In the below syntax it only shows the values which starts with the letter ”B” and have atleast 2 characters length.

LIKE ‘B_%’

LIKE ’b__%’ — finds any value that starts with “b” and are atleast 3 characters in length.

LIKE ’b__%’

LIKE ‘C%s’ — finds any values that start with “C” and ends with “s”.

It only shows the values which starts with “C” and ends with “s” as per specified condition.

LIKE ‘C%s’

Another example : LIKE ‘J%s’

Just playing around…… with LIKE operator

In the below syntax it only shows where the letter “e” is in 4th position from “color column”. LIKE ‘___e%’ (3 underscores)

LIKE ‘___e%’

Overhere it shows the values which start with “B” from “color column” and have atleast 3 characters in it. LIKE ‘B____%’

LIKE ‘B____%’

In this syntax it finds the letter “n” which is in any position from “Article column”. LIKE ‘%n%.

LIKE ‘%n%’

Overhere it starts with letter “p” and ends with “h”. It all depends on the specified condition. LIKE ‘p%h’.

LIKE ‘p%h’