w3resource logo


SQL wildcards and like operator

SQL wildcards and LIKE Operator

rating Average rating 8 out of 10. Total 25 users rated.

<<PreviousNext>>

Description

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.

Syntax

SELECT [* | column_list]

FROM <table_name> WHERE ( column_name)

LIKE <wildcards>;

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.
wildcards Types of wildcards.

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

Sql wildcards percentage (%)

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

Sql wildcards percentage (%) with boolean NOT

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.

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database



<<PreviousNext>>