LIKE operator in SQL

LIKE operator is used to filter data based on some specific pattern.

It is used with the WHERE clause and searches for a particular pattern in a column.

Common use: Searching & filtering data to show in tables

Syntax: SELECT * FROM table_name WHERE column_name LIKE 'pattern’

The LIKE command is not used alone i.e., it uses some special characters (wildcard characters) to specify a pattern.

Wildcard Characters

1. The percent sign (%)

Meaning: It matches zero, one, or multiple characters (capital or small) or numbers.

Examples:

  1. ‘T%’ will find all strings starting with ‘T’ and having any number of other characters after it.
  2. ‘%T’ will find all strings ending with ‘T’ and having any number of other characters before it.
  3. ‘%tjwebdev%’ will find all strings having ‘tjwebdev’ at any position.
  4. ‘T%J’ will find all strings starting with ‘T’ and ending with ‘J’.
Syntax: SELECT * FROM table_name WHERE column_name LIKE 'T%’
2. The underscore sign (_)

Meaning: It represents a single character or number.

Example:

  1. ‘_’ will find all strings having only one character (any).
  2. ‘__’ will find all strings having only two characters(any).
  3. ‘T_’ will find all strings having two characters with ‘T’ as the first character and the second character can be anything.
Syntax: SELECT * FROM table_name WHERE column_name LIKE 'T_’

Combining Wildcard Example

  1. ‘_t%’ will find all strings with ‘t’ at second position
  2. ‘t_%’ will find all strings starting with ‘t’ and has at least two characters.
Syntax: SELECT * FROM table_name WHERE column_name LIKE 't_%’

Just use the wildcards and create patterns!

Share