Home arrow Oracle arrow Page 7 - Mastering the WHERE Clause

Regular Expressions and Handling NULL - Oracle

Gain the full power of SQL to write queries in an Oracle environment with this updated book (new information on Oracle 10g). This chapter focuses on the role of the WHERE clause in SQL statements and the various options available when building a WHERE clause. (Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu, O'Reilly, ISBN: 596006322.)

  1. Mastering the WHERE Clause
  2. WHERE to the Rescue
  3. WHERE Clause Evaluation
  4. Conditions and Expressions
  5. Membership Conditions and Range Conditions
  6. Matching Conditions
  7. Regular Expressions and Handling NULL
  8. Placement of Join Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 22
October 19, 2004

print this article



Beginning with the Oracle Database 10g release, you can use regular expressions within your conditions. Regular expressions allow for much more complex pattern matching without the need for multiple conditions. For example, if you wanted to find all customers whose name begins with W, ends in “ies” and does not include L anywhere in the string, you could use multiple conditions with the LIKE and NOT LIKE operators:

FROM customer
WHERE name LIKE 'W%ies'
  AND name NOT LIKE '%L%';

Worcester Technologies
Wallace Industries

You can achieve the same result more succinctly, in a single expression, with the new REGEXP_LIKE function:

FROM customer
WHERE REGEXP_LIKE(name, '^W([^L]*)ies$');

Worcester Technologies
Wallace Industries

If that second argument to REGEXP_LIKE looks like gibberish, fear not: we cover regular expressions in detail in Chapter 17.

Handling NULL

The NULL expression represents the absence of a value. If, when entering an order into the database, you are uncertain when the order will be shipped, it is better to leave the ship date undefined than to fabricate a value. Until the ship date has been determined, therefore, it is best to leave the ship_dt column NULL. NULL is also useful for cases where data is not applicable. For example, a cancelled order’s shipping date is no longer applicable and should be set to NULL.

When working with NULL, the concept of equality does not apply; a column may be NULL, but it will never equal NULL. Therefore, you will need to use the special operator IS NULL when looking for NULL data, as in:

UPDATE cust_order
SET expected_ship_dt = SYSDATE + 1
WHERE ship_dt IS NULL;

In this example, all orders whose shipping date hasn’t been specified will have their expected shipping date set to tomorrow.

You may also use the IS NOT NULL operator to locate non-NULL data:

UPDATE cust_order
SET expected_ship_dt = NULL

This example sets the expected shipping date to NULL for all orders that have already shipped. Notice that the SET clause uses the equality operator (=) with NULL, whereas the WHERE clause uses the ISNOT NULL operator. The equality operator is used to set a column to NULL, whereas the ISNOT NULL operator is used to evaluate whether a column is NULL. A great many mistakes might have been avoided had the designers of SQL chosen a special operator to be utilized when setting a column to NULL (i.e., SET expected_ship_dt TO NULL), but this is not the case. To make matters worse, Oracle doesn’t complain if you mistakenly use the equality operator when evaluating for NULL. The following query will parse and execute but will never return rows:

SELECT order_nbr, cust_nbr, sale_price, order_dt
FROM cust_order
WHERE ship_dt

Hopefully, you would quickly recognize that the previous query never returns data and replace the equality operator with ISNULL. However, there is a more subtle mistake involving NULL that is harder to spot. Say you are looking for all employees who are not managed by Marion Blake, whose employee ID is 7698. Your first instinct may be to run the following query:

SELECT fname, lname, manager_emp_i d
FROM employee
WHERE manager_emp_id != 7698;

-------------- ----------- ---------------
JOHN           SMITH       7902
TERRY          JONES       7839
MARION         BLAKE       7839
CAROL          CLARK       7839
DONALD         SCOTT       7566
DIANE          ADAMS       7788
JENNIFER       FORD        7566
BARBARA        MILLER      7782

While this query returns rows, it leaves out those employees who are top-level managers and, thus, are not managed by anyone. Since NULL is neither equal nor not equal to 7698, this set of employees is absent from the result set. To ensure that all employees are considered, you will need to explicitly handle NULL, as in:

SELECT fname, lname, manager_emp_id
FROM employee
WHERE manager_emp_id IS NULL OR manager_emp_id != 7698;

-------------- ----------- ---------------
JOHN           SMITH       7902
TERRY          JONES       7839
MARION         BLAKE       7839
CAROL          CLARK       7839
DONALD         SCOTT       7566
DIANE          ADAMS       7788
JENNIFER       FORD        7566
BARBARA        MILLER      7782

Including two conditions for every nullable column in your WHERE clause can get a bit tiresome. Instead, you can use Oracle’s built-in function NVL, which substitutes a specified value for columns that are NULL, as in:

SELECT fname, lname, manager_emp_i d
FROM employee
WHERE NVL(manager_emp_id, -999) != 7698;

-------------- ----------- ---------------
JOHN           SMITH       7902
TERRY          JONES       7839
MARION         BLAKE       7839
CAROL          CLARK       7839
DONALD         SCOTT       7566
DIANE          ADAMS       7788
JENNIFER       FORD        7566
BARBARA        MILLER      7782

In this example, the value -999 is substituted for all NULL values, which, since -999 is never equal to 7698, guarantees that all rows whose manager_emp_id column is NULL will be included in the result set. Thus, all employees whose manager_emp_id column is NULL or is not NULL and has a value other than 7698 will be retrieved by the query.  

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More Oracle Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: