w3resource logo

SQL wildcards and like operator

SQL wildcards and LIKE Operator


Seondary Nav


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 :

Wildcards Description
% The percent sign character (%) represent sequence of 0 (zero) or more characters.
Underscore ( _ ) The underscore character ( _ ) represents a single character.
[charlist] It represents any single character within a charlist
[^charlist] or [!charlist] It represents any single character other than the charlist

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 with in any valid SQL statement, such as SELECT, INSERT INTO, UPDATE or DELETE.


SELECT [* |  column_list] 
FROM <table_name> WHERE ( column_name)
LIKE <wildcards>;


Name Description
column_list List of columns of a table.
table_name Name of the table.
column_name Name of the column which will participate in action with LIKE operator.
wildcards Types of wildcards.

Pictorial Presentation

SQL LIKE Operator pictorial presentation

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%';


---------- -------------------- --------------- -------------------
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

Sql wildcards percentage (%) with boolean NOT

Sample table : agents

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

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

the following sql statement can be used :

FROM agents
WHERE agent_name  NOT LIKE 'M%';


---------- -------------------- --------------- ---------- --------------- --------
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

Equals(=) vs. LIKE

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

FROM agents
WHERE commision = .11;

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

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.

See our Model Database



Looking for some other tutorial?