CODEWITHSUNDEEP
sqlreplacenull
user3281385
user3281385

Reputation: 119

Replacing any NULL fields with a string

I'm new to SQL and this problem has stumped me. I am supposed to write an SQL statement that lists all the data in a table, but if a column might contain NULL values (in this case only one field is NULL), the phrase "-NONE-" must print in that column for that row. The lab hints that I must use one or more single-row functions to accomplish this.

We are using Oracle SQL Developer to test scripts. I am trying to use the REPLACE function but I keep getting errors. I've tried using NVAL, REPLACE, and other similar functions but I am clearly getting the syntax wrong. For example:

SELECT *, REPLACE(manager_id, NULL, '-NONE-')
FROM departments;

^Always returns an error: missing FROM statement where expected

Upvotes: 1

Views: 1578

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The function you want is coalesce(). This is the ANSI standard function.

Assuming your variable is a character string:

SELECT d.*, COALESCE(manager_id, '-NONE-')
FROM departments d;

If it is not a string, then convert it first:

SELECT d.*, COALESCE(cast(manager_id as varchar(255)), '-NONE-')
FROM departments d;

Upvotes: 4

Barranka
Barranka

Reputation: 21047

maybe this might help you:

select 
    *, 
    (case when manager_id is null then '-none-' else manager_id end) as manager_id_2
from 
    departments

Upvotes: 0

Related Questions