SQL wildcards and like operator
has average rating
8
out of 10.
Total 14 users rated.
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 (%) 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
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.
