CODEWITHSUNDEEP
sqloracle-database
WeVie
WeVie

Reputation: 598

Case statement not working as expected in Oracle

I have a field in the database that contains a lot of empty/null rows. I am trying to create a case statement that changes this to "Empty Field" or something similar so that when it is displayed to the user, they will have an indication that the row has no data for that column but for some reason it is not working. When I select the data without the case statement, I get either a string or an empty field as should be. When I add the case statement, every row comes back as {null} regardless to what is in that column. I have used similar case statements many times but this has me baffled.

SELECT CASE
         WHEN col_name = TRIM('') THEN 'Empty Field'
         END col_name
FROM table_name

I also tried LIKE in place of the equals sign as well as NULL instead of TRIM('')

working solution:

SELECT NVL(TRIM(col_name), 'Empty Field') AS col_name
FROM table_name

Upvotes: 3

Views: 15323

Answers (3)

psaraj12
psaraj12

Reputation: 5072

The correct thing to do is to use codenheim example.But for the sake of clarity when you check a NULL column in oracle you have to use IS NULL

SELECT CASE
     WHEN col_name IS NULL THEN 'Empty Field'
     ELSE col_name
     END AS col_name
FROM table_name;

Upvotes: 0

mrjoltcola
mrjoltcola

Reputation: 20842

In Oracle, '' in VARCHAR context is treated as NULL

You can just use NVL(col_name,'Default Value')

select NVL(col_name, 'Empty Field') from table_name

TRIM('') is NULL in Oracle SQL, so has no effect.

SQL> set null NULL
SQL> select trim('') from dual;

T
-
N
U
L
L

As far as your CASE statement, the problem lies in your use of equalty with NULL. You cant say where NULL = anything, it is false. NULL is not equal to anything, not even itself. You need to use:

WHERE col_name IS NULL

IS NULL and = NULL are not the same.

SQL> select case when id = trim('') then 'Empty' end from nulls;

CASEW
-----
NULL

SQL> select case when id = null then 'Empty' end from nulls;

CASEW
-----
NULL

SQL> select case when id is null then 'Empty Field' end from nulls;

CASEW
-----
Empty Field      <--- DATA!

If this does not work, perhaps you have spaces in your data. Test that by using SQLPLUS - SET NULL NULL command I showed to verify it is truly NULL.

Upvotes: 6

Alex Poole
Alex Poole

Reputation: 191265

You should use nvl() as codenheim suggested, but further to the answers about your original solution, you have no else clause - so nothing is matching against = null and you never get Empty field, but everything that doesn't match is then defaulted to null anyway. You'd need to do:

SELECT CASE
     WHEN col_name IS NULL THEN 'Empty Field'
     ELSE col_name
     END AS col_name
FROM table_name

But that would be the same as

SELECT NVL(col_name, 'Empty Field') AS col_name
FROM table_name

You suggested that still isn't working but weren't very specific; but your original use of trim makes me wonder if you have some values that are not null but only consist of spaces - so they would still just be spaces afterwards. If that is the case and you want to treat all-spaces as null then you could do:

SELECT NVL(TRIM(col_name), 'Empty Field') AS col_name
FROM table_name

If you have other whitespace - just a tab perhaps - then you could use a regular expression to strip that out instead, e.g.:

SELECT NVL(REGEXP_REPLACE(col_name, '^[[:space:]]+$', NULL),
    'Empty Field') AS col_name
FROM table_name;

SQl Fiddle demo.

Upvotes: 4

Related Questions