SQL LIKE Operator

LIKE Operator

LIKE is the ANSI/ISO standard operator for comparing a column value to another column value, or to a quoted string. Returns either 1 (TRUE) or 0 (FALSE)

The SQL LIKE operator is only applied on a field of types CHAR or VARCHAR to match a pattern.

To match a pattern from a word, special characters, and wildcards characters may have used with LIKE operator.

The LIKE operator can be used within any valid SQL statement, such as SELECT, INSERT INTO, UPDATE or DELETE.

Syntax:

SELECT [* |  column_list] 
FROM <table_name> WHERE expression [NOT] LIKE pattern 
[ESCAPE escape_sequence]

Parameters:

SQL Wildcards:

The SQL wildcards can be used to search data within a table.

SQL wildcards are used with SQL LIKE operator.

The boolean NOT operator in the select statement can be used as wildcard NOT LIKE operator.

In SQL, the wildcards are:

Pictorial Presentation:SQL LIKE Operator

Examples: SQL LIKE Operator

SQL wildcards percentage (%)

Sample table: customer

To get the 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following condition -

1. the 'cust_name' must begin with the letter 'S',

the following SQL statement can be used :

SELECT cust_code,cust_name,cust_city,cust_country
FROM customer
WHERE cust_name LIKE 'S%';

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- -------------------
C00015     Stuart               London          UK
C00005     Sasikant             Mumbai          India
C00006     Shilton              Torento         Canada
C00017     Srinivas             Bangalore       India
C00012     Steven               San Jose        USA
C00011     Sundariya            Chennai         India

Relational Algebra Expression:

Relational Algebra Tree:

SQL wildcards percentage (%) with boolean NOT

Sample table: agents

To get all the columns from the table 'agents' with the following condition -

1. the 'agent_name' not initiated with the letter 'M',

the following SQL statement can be used :

SELECT *
FROM agents
WHERE agent_name  NOT LIKE 'M%';

Output:

AGENT_CODE AGENT_NAME           WORKING_AREA    COMMISSION PHONE_NO        COUNTRY
---------- -------------------- --------------- ---------- --------------- --------
A003       Alex                 London                 .13 075-12458969
A001       Subbarao             Bangalore              .14 077-12346674
A009       Benjamin             Hampshair              .11 008-22536178
A007       Ramasundar           Bangalore              .15 077-25814763
A008       Alford               New York               .12 044-25874365
A011       Ravi Kumar           Bangalore              .15 077-45625874
A010       Santakumar           Chennai                .14 007-22388644
A012       Lucida               San Jose               .12 044-52981425
A005       Anderson             Brisban                .13 045-21447739
A004       Ivan                 Torento                .15 008-22544166

Relational Algebra Expression:

Relational Algebra Tree:

Equals(=) vs. LIKE

The equals to(=) operator is a comparison operator and used for equality test within two numbers or expressions. For example :

SELECT *
FROM agents
WHERE commision = .11;

LIKE operator checks whether a specific character string matches a specified pattern. For example:

SELECT *
FROM agents
WHERE agent_name LIKE 'Sant%'

LIKE is generally used only with strings and equals (=) is used for exact matching and it seems faster.

Relational Algebra Expression:

Relational Algebra Tree:

Example : SQL wildcards underscore ( _ )

The underscore character ( _ ) represents a single character to match a pattern from a word or string. More than one ( _ ) underscore characters can be used to match a pattern of multiple characters.

Sample table : customer

To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions -

1. the 'cust_name' must initiate with the letter 'R',

2. the third letter of 'cust_name' must be 'm',

3. and the second letter of 'cust_name' may be any,

the following SQL statement can be used :

SELECT cust_code,cust_name,cust_city,cust_country
FROM customer
WHERE cust_name LIKE 'R_m%';

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- -------------------
C00007     Ramanathan           Chennai         India
C00009     Ramesh               Mumbai          India

Relational Algebra Expression:

Relational Algebra Tree:

SQL wildcards underscore ( _ ) multiple characters

Sample table : customer

To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions -

1. the first three letters of 'cust_name' may be any letter

2. the forth letter of 'cust_name' must be 'l'

3. and the rest may be any

the following SQL statement can be used:

SELECT cust_code,cust_name,cust_city,cust_country
FROM customer
WHERE cust_name LIKE '___l%';

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- ------------------
C00023     Karl                 London          UK
C00006     Shilton              Torento         Canada

Relational Algebra Expression:

Relational Algebra Tree:

SQL wildcards underscore ( _ ) for specific length

Sample table: customer

To get 'cust_code', 'cust_name', 'cust_city' and 'cust_country' from the table 'customer' with following conditions -

1. the first three letters of 'cust_name' may be any letter

2. the forth letter of 'cust_name' must be 'l'

3. and the string must be a length of 4 letters

the following SQL statement can be used :

SELECT cust_code,cust_name,cust_city,cust_country
FROM customer
WHERE cust_name LIKE  '___l';

Output:

CUST_CODE  CUST_NAME            CUST_CITY       CUST_COUNTRY
---------- -------------------- --------------- --------------------
C00023     Karl                 London          UK

Relational Algebra Expression:

Relational Algebra Tree:

Example: SQL [charlist] wildcards

The [charlist] WILDCARDS are used to represent any single character within a charlist.

The [^charlist] and [!charlist] WILDCARDS is used to represents any single character, not in the charlist.

Sample table : agents

To get all rows from the table 'agents' with the following condition -

1. the 'agent_name' must begin with the letter 'a' or 'b' or 'i'

the following SQL statement can be used :

SELECT *
FROM agents
WHERE agent_name  LIKE '[abi]%';

Example: SQL [^charlist] wildcards

Sample table: agents

To get all rows from the table 'agents' with the following condition -

1. the 'agent_name' must not begin with the letter 'a' or 'b' or 'i',

the following SQL statement can be used :

SELECT *
FROM agents
WHERE agent_name  LIKE '[^abi]%';

Example: SQL [!charlist] wildcards

Sample table: agents

To get all rows from the table 'agents' with the following condition -

1. the 'agent_name' must not begin with the letter 'a' or 'b' or 'i',

the following SQL statement can be used :

SELECT *
FROM agents 
WHERE agent_name  LIKE '[!abi]%';

See our Model Database

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.