Skip to main content

SQL LIKE Operator

Body

The SQL provides us with the option to retrieve a specific set of data from the table, based on matching it with some sort of string with which we are unsure how it is stored in the database, e.g. if one does not know what is the actual spelling of certain thing, or he simply wants to search the database on the given values, the LIKE operator can be used in building a simple search bar on the website, which will get all the results from the database that matched the string given in the search Bar.

The LIKE operator is very helpful but it does take a time to fetch data, actually it is a bit slow on retrieving data as it compares all the values of the column with the mentioned value, the delay is not noticeable on small tables, but when it comes to huge tables containing million of records then the LIKE operator delay is noticeable. Now let’s see how we can write a LIKE operator.

SQL LIKE Syntax

SELECT column_name(s) FROM table_name WHERE column_name LIKE "SOME CONIDTION"

Consider the following table

Firstname Lastname Age Marital Status Country
John Smith 40 Married Usa
Mathew Simon 30 Married Uk
Bill Steve 20 Single Usa
Amanda Rogers 28 Married Germany
Steve Hills 30 Single France

Example # 1

SELECT Firstname,Lastname FROM users WHERE Lastname LIKE "S%"

Result of the Query

Firstname Lastname
John Smith
Mathew Simon
Bill Steve

This Result of the query is including all the data set whose last name Start with "S"

Example # 2

SELECT Firstname,Lastname FROM users WHERE Lastname LIKE "%ill%" OR Firstname LIKE "%ill%"

Result of the Query

Firstname Lastname
Bill Steve
Steve Hills

This Result of the query is including all the data set whose first name or last name contains the charcter set "ill"

Example # 3

SELECT Firstname,Lastname FROM users WHERE Lastname LIKE "%e"

Result of the Query

Firstname Lastname
Bill Steve

This Result of the query is including all the data set whose Last name End with the Character "e"

What is % In the Like?

The % Sign in the LIKE operator is a wildcard which you will learn better when you study the wildcard tutorial

Add new comment