w3resource logo


SQL wildcards and like operator

SQL LIKE Operator

Secondary Nav

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 with in 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 :

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.
WHERE Tests whether expression matches the pattern. Both expression and pattern may be any valid expression and are evaluated to strings. Use NOT LIKE to test if a string does not match a pattern.
Patterns may use the following SQL wildcard characters :

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 :

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

Pictorial Presentation : SQL LIKE Operator

SQL LIKE Operator

Go Top

Examples : SQL LIKE Operator

Contents:

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

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 :

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

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.

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

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

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

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



Is this content useful for you?