How to Replace Null Values with a Text

How to replace null values with a text?

You can use case expression:

select last_name
     , case when commision_pct is null then 'No Commission' else commision_pct end    
from employees;

or coalesce:

select last_name
     , coalesce(commision_pct, 'No Commission')
from employees;

or nvl:

 select last_name
     , nvl(commision_pct, 'No Commission')
from employees;

P.S. In case commision_pct's datatype is not varchar you should also use cast or to_char.

Replace null value as text in the original table

You can give each column in your result a name:

SELECT
  rating AS rating_with_nulls,
  NVL(rating, 'NR') AS rating
FROM film
ORDER BY rating_with_nulls;

This is called a column alias. The AS is optional.

You find it in the syntax diagrams in the docs here: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702. Look for "select_list" and you'll see expr [AS] c_alias. It may take some time to get used to these diagrams, but then they become really helpful.

Sometimes it is not a good idea to name the result like a column. In above example, what if we had ORDER BY rating? What would rating refer to? The original column or the expression result where nulls are replaced with 'NR'?

NVL is Oracle specific by the way. The standard SQL function for this situation is COALESCE. It is okay to use NVL, but it's always good to know that it is Oracle only and what to use in another DBMS, I think.

Replacing NULLs with 'NA' in a SELECT

The problem is possibly due to type conversion. Try:

select coalesce(cast(value as varchar(255)), 'N/A')

A column/expression can have only one type. So, if value is not a string, then your code tries to convert 'N/A' to some numeric value.

SQL Replace NULL Values with Own text

I've decided to do it differently since I wasn't going anywhere with the above SQL. I'll appreciate if anyone has suggestions to make for the above SQL with the set constraints.

SELECT 
  band.name AS Band_Name, 'NULL' AS Keyboard_Player
FROM 
  memberof
INNER JOIN 
 member
ON 
  memberof.mid = member.mid 
FULL JOIN 
  band
ON 
  memberof.bid = band.bid 
AND 
  instrument = 'keyboards'

WHERE  
  member.name IS NULL 

UNION

SELECT 
  band.name AS Band_Name, member.name AS Keyboard_Player
FROM 
  memberof
INNER JOIN 
 member
ON 
  memberof.mid = member.mid 
FULL JOIN 
  band
ON 
  memberof.bid = band.bid 
WHERE
  instrument = 'keyboards'

Replace NULL Values With a Blank Value

You have to convert uniqueidentifier to string type.

isnull(convert(char(36),COB.ParentIdLevel1),'') as 'Parent Option',
isnull(convert(char(36),COB.ParentIdLevel2),'') as 'Parent Option 1',
isnull(convert(char(36),COB.ParentIdLevel3),'') as 'Parent Option 2',
isnull(convert(char(36),COB.ParentIdLevel4),'') as 'Parent Option 3',

How to replace NULL values with Mean value of a category in SQL?

You can use the AVG window function, that will partition on the three column of interest and replace null values using the COALESCE function:

SELECT appointment_date,
       patient_id,
       practitioner_id,
       appointment_duration_min,
       COALESCE(revenues_from_appointment, 
                AVG(revenues_from_appointment) OVER(PARTITION BY patient_id, 
                                                                 practitioner_id, 
                                                                 appointment_duration_min))
FROM tab

Try it here.

---

Related Topics