SQL: Output default string for NULL column

Question

I am playing with sqlplus command prompt, I want to display a default string, lets say 'ITISNULL' for all NULL columns without updating them. select * from enrollments where ....

Enrollments is a table which might contain null in its lgrade column. I dont want to update it but just want an output string say, "to be graded" to be printed in its place.

Is there any SQL function I can use for this?

score 3 · Accepted Answer · answered Apr 08 '13 at 18:41

3

There are a couple ways to do this. One option is to use COALESCE:

SELECT COALSECE(lgrade, 'to be graded')...

You can't specify * for all fields, you'll have to specify each column name accordingly.

answered Apr 08 '13 at 18:41

sgeddes

62,311
6
61
83

another great way to do it. Never tried testing it, but I wonder if one is faster than the other (most likely not) – Limey Apr 08 '13 at 18:44
@Limey -- Here is a great post about the differences: http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce – sgeddes Apr 08 '13 at 18:50

score 1 · Answer 2 · answered Apr 08 '13 at 18:42

1

NVL is your answer, you will need to do for every column that could have nulls values

http://www.techonthenet.com/oracle/functions/nvl.php

answered Apr 08 '13 at 18:42

Limey

2,642
6
37
62

score 1 · Answer 3 · answered Apr 08 '13 at 18:50

In SQL*Plus you can use SET NULL:

SQL> SET NULL 'ITISNULL'
SQL> SELECT ...

All NULL results will display as ITISNULL. The only problem is that columns with a width of less than 8 (the length of ITISNULL) may wrap - at least they do in my older version of SQL*Plus (9.2).

To return SQL*Plus to its default, issue the following:

SQL> SET NULL ''

Max · Answer 4 · 2013-04-08T20:47:55.197

0

there are plenty of ways to show a string when the column is null. As mentioned above, some of the ways :-

Use NVL to display 'itisnull' whenever the value of col1 is null.
```
select nvl(col1, 'itisnull') from tableName;
```

Use CASE to display 'itisnull' whenever the value of col1 is null.

select
 case when col1 is null then
 'itisnull'
 else col1
 end colum from tableName;

edited Apr 08 '13 at 20:47

answered Apr 08 '13 at 18:55

Max

4,067
1
18
29

I almost upvoted this but I'm not sure about the if-esle part. I took a quick look at the docs and I'm not convinced that this is an appropriate place to use that syntax. – Dan Bracuk Apr 08 '13 at 19:25
1

you forgot two of my favorites `NVL2` and `DECODE` – haki Apr 09 '13 at 12:12

SQL: Output default string for NULL column

4 Answers4