CODEWITHSUNDEEP
sqloracle-databaseoracle11g
David Brower
David Brower

Reputation: 3048

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.

Upvotes: 1

Views: 4921

Answers (2)

Zeina
Zeina

Reputation: 1603

Try this way:

SELECT * 
FROM test 
WHERE SOU_USER = 'proust'
AND SOU_TYPE = 'S' 
AND TRIM(sou_ordref) = ''

Since an empty char is different than NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 3

Related Questions