SQL Replace All Nulls

SQL replace all NULLs

As many here have said, the best approach is ISNULL(), however if you want an easy way to generate all those ISNULL()'s use the following code:

SELECT 'ISNULL([' + COLUMN_NAME + '], ' + 
  CASE 
    WHEN DATA_TYPE = 'bit' THEN '0'
    WHEN DATA_TYPE = 'int' THEN '0'
    WHEN DATA_TYPE = 'decimal' THEN '0'
    WHEN DATA_TYPE = 'date' THEN '''1/1/1900'''
    WHEN DATA_TYPE = 'datetime' THEN '''1/1/1900'''
    WHEN DATA_TYPE = 'uniqueidentifier' THEN '00000000-0000-0000-0000-000000000000'
    ELSE '''''' -- everything else get's an empty string
  END + ') AS [' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableName'

This will make the tedious job a whole lot easier, you then just have to edit the output to account for the various field types (int, varchar, dates, etc)

Edit: accounting for various datatypes with default values..

Replacing NULL with 0 in a SQL server query

When you want to replace a possibly null column with something else, use IsNull.

SELECT ISNULL(myColumn, 0 ) FROM myTable

This will put a 0 in myColumn if it is null in the first place.

SQL - replace a NULL value with a calculated value

Like this:

SELECT ticker, 
    case WHEN price_target IS NULL THEN ((open * 0.1) + open) else price_target 
    END as price_target, 
    open 
FROM rating_open 

Function to REPLACE* last previous known value for NULL

Assuming the number you have is always increasing, you can use MAX aggregate over a window:

SELECT dt
     , country
     , cnt
     , MAX(cnt) OVER (PARTITION BY country ORDER BY dt)
  FROM #data

If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:

SELECT dt
     , country
     , cnt
     , SUM(cnt) OVER (PARTITION BY country, partition)
FROM (
    SELECT country
         , dt
         , cnt
         , SUM(CASE WHEN cnt IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY country ORDER BY dt) AS partition
    FROM #data
) AS d
ORDER BY dt

Here's a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you'll see MAX(...) method producing wrong results.

Replace null with 0 in MySQL

Yes, by using COALESCE.

SELECT COALESCE(null_column, 0) AS null_column FROM whatever;

COALESCE goes through the list of values you give it, and returns the first non-null value.

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