David Brower David Brower - 2 months ago 13
SQL Question

Empty Char in Where Clause?

I have the following table:

CREATE TABLE SOAUDIT
(SOU_USER CHAR(8 BYTE),
SOU_ORDREF CHAR(8 BYTE),
SOU_TYPE CHAR(1 BYTE),
SOU_DESC CHAR(50 BYTE))


There is a unique index defined on the first three columns (but no primary key, which is something we have no control over).

And in the table there are some records:

| SOU_USER | SOU_ORDREF | SOU_TYPE | SOU_DESC |
|----------|------------|----------|------------------|
| proust | | S | recherche |
| joyce | 12345678 | S | pelurious |
| orwell | 19841984 | T | doubleplusungood |
| camus | 34598798 | P | peiner |


On closer inspection it appears that the value in SOU_ORDREF for user 'proust' is an empty char string of 8 characters.

Now, what I need to be able to do is to query this table based on their unique values (which I will receive from a SQL Server database (just to complicate matters nicely). In the case of SOU_ORDREF the search value will be a blank field:

SELECT *
FROM SOAUDIT
WHERE (SOU_USER, TRIM(SOU_ORDREF), SOU_TYPE)
IN (('proust', null, 'S'))


This doesn't return the record I am looking for.

When I rewrite the query as following:

SELECT *
FROM SOAUDIT
WHERE (SOU_USER, SOU_TYPE)
IN (('proust', 'S'))
AND TRIM(sou_ordref) is null


Then I do get the desired record.

However, I want to be able to pass in more than one record into the
WHERE
clause so the second version doesn't really help.

Answer

Oracle -- by default -- treats empty strings and NULL as the same thing.

This can cause awkward behavior, because comparisons to NULL almost never return true. So a simple expression such as where sou_ordref = '' never returns true, because it is equivalent to where sou_ordref = NULL.

Here is one workaround:

SELECT * 
FROM SOAUDIT 
WHERE (SOU_USER, COALESCE(TRIM(SOU_ORDREF), ' '), SOU_TYPE) IN
          ( ('proust', ' ', 'S') ) 

Note that this replaces the empty string (NULL) with a space. It then compares the results to a space.