11.6 How do I…Use wildcards in a query?Problem
I want to use wildcard characters in some of my queries. As an example, I want to return all records in which the first three letters of the person’s last name are JON. The user of the system does not always know the exact spelling of the data in a character field, and pattern matching is necessary. How do I use wildcards in a query?
Technique
Oracle contains two special characters used in pattern matching operations. The percent sign (%) is used to specify any number of unknown characters. The underscore (_) is a placeholder character and can be replaced by only one character in the specified location.
Wildcard characters must be used with the LIKE operator. If a wildcard character is used with an equal sign (=), it is taken literally. The LIKE operator specifies that wildcards can be used in the specified string. If the LIKE operator is used and no wildcards are specified, it is equivalent to the equal sign.
Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP11_14.SQL, shown in Figure 11.14, creates the sample tables and data used in this How-To.
The sample table is populated with data to demonstrate the use of wildcard characters. Run the file to create the sample table and data.
SQL> START CHP11_14.sql
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.
2. Load CHP11_15.SQL into the SQL buffer. The file contains a query that uses a wildcard character to return all rows in which the first three letters of the LAST_NAME column are JON.
SQL> GET CHP11_15.sql
1 SELECT
2 LAST_NAME,
3 FIRST_NAME
4 FROM
5 EMP11
6 WHERE
7* LAST_NAME LIKE ‘JON%’
Lines 2 and 3 specify the columns returned by the query. Line 5 specifies the source table for the data. Line 7 uses the LIKE operator and the % wildcard character in the WHERE clause of the query. The query returns all records in which the LAST_NAME column begins with the letters JON.
3. Run the statement to view the results.
SQL> /
LAST_NAME
FIRST_NAME
-------------------
--------------------
JONSON
BILL
JONES
MARY
4. Load CHP11_16.SQL into the SQL buffer. The file contains a query that uses the _ wildcard character to replace a single letter in a string.
SQL> GET CHP11_16.SQL
1 SELECT
2 LAST_NAME
3 FROM
4 EMP11
5 WHERE
6* LAST_NAME LIKE ‘JO_ES’
The _ wildcard character in line 6 specifies that only the third letter in the name can be replaced in the query. The first two letters in the LAST_NAME column must be JO and the final two letters must be ES. The column must also be five characters long.
5. Run the statement to display the results.
SQL> /
LAST_NAME
--------------------------
JONES
JOLES
How It Works
Step 1 creates the sample table and records used in this How-To. Steps 2 and 3 use the % wildcard character to replace any number of characters. The query returns all records in which a column begins with a specific string. Steps 4 and 5 use the _ wildcard character to specify that only a single character can be replaced in the query.
Comments
Wildcard characters make it easier to find data in a table. The % character is equivalent to the * character in UNIX or DOS. Remember to use the LIKE operator when working with wildcard characters or else they will be treated as literal values.