CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Alec
Alec

Reputation: 643

SQL replace all NULLs

I have a big table with some NULLs in it. What is the easiest possible way to select from the table with 0's instead of NULLs.

Or if there is no easy way to do that, I'm willing to physically replace all the nulls with 0's in the table.

There is lots of columns, I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.

Upvotes: 16

Views: 60425

Answers (9)

Josh Weatherly
Josh Weatherly

Reputation: 1730

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..

Upvotes: 27

Christophxr
Christophxr

Reputation: 21

Be a boss. Write something like:

select 'update ' + table_name + ' set [' + column_name + '] = '''' where [' + column_name + '] is null'
from tempdb.information_schema.columns
where table_name = 'YourTableName'

It'll spit out a big ol' query for you.

Upvotes: 1

william-1066
william-1066

Reputation: 449

I wanted a dynamic query to do the update option. Borrowing from the post above and another I have the following:

            USE [YOUR DATABASE] 
            DECLARE @tableName nvarchar(100) 
            DECLARE @name varchar(50) 
            DECLARE @dtype varchar(50) 
            DECLARE @CMD NVARCHAR (200) 

            SET @tableName = [YOUR TABLE NAME] 


            DECLARE db_cursor CURSOR FOR 

                  SELECT c.name, t.name AS Dtype 
                  FROM sys.columns c 
                  INNER JOIN sys.types t 
                  ON t.system_type_id = c.system_type_id 
                  WHERE c.[object_id] = 
                      (SELECT [object_id] FROM sys.objects WHERE type = 'U' AND [NAME] = @tableName) 

            OPEN db_cursor 
            FETCH NEXT FROM db_cursor INTO @name, @Dtype 

            WHILE @@FETCH_STATUS = 0 BEGIN

            SET @CMD = 'UPDATE ' + @tableName + ' SET ' + quotename(@name) +' = ' + 

               (CASE 
                 WHEN (@Dtype = 'bit') THEN '0' 
                 WHEN (@Dtype = 'int') THEN '0' 
                 WHEN (@Dtype = 'decimal') THEN '0' 
                 WHEN (@Dtype = 'date') THEN '''1/1/1900''' 
                 WHEN (@Dtype = 'datetime') THEN '''1/1/1900'''
                 WHEN (@Dtype = 'uniqueidentifier') THEN '00000000-0000-0000-0000-000000000000' 
              ELSE '''''' 
              END ) 
            + ' WHERE ' + quotename(@name) +' IS NULL' 

            PRINT @CMD 

            EXEC sp_executeSQL @cmd 

            FETCH NEXT FROM db_cursor INTO @name, @Dtype 

            END 

            CLOSE db_cursor 
            DEALLOCATE db_cursor

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.

Take the hit and do this once for each one:

ALTER TABLE TableName ALTER COLUMN FieldName INTEGER DEFAULT 0 NOT NULL;

You can then forget all about ISNULL, COALESCE, three-valued logic, etc.

Upvotes: 0

Purplegoldfish
Purplegoldfish

Reputation: 5284

You have two options really

ISNULL(yourColumn,0) AS columnName

or to actually fix the issue and change all the NULL data to 0

UPDATE table
SET column = 0
WHERE
column IS NULL    -- remember "column = NULL" always equates to NULL!

When you do this dont forget the WHERE or everything will end up being 0!

Upvotes: 12

Stephen Turner
Stephen Turner

Reputation: 7314

It's not easy to do it without going through each column just as you said.

NULL has a special and distinct meaning to 0. It means there is no value or unknown as opposed to a known zero value. Some database purists would argue that a row in a table shouldn't have NULLs in at all!

If the NULLs in your source table really mean zero then change them, otherwise deal with the nulls as they propogate through your queries.

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

Alternatively to ISNULL, you can use COALESCE, which is ANSI SQL compatible.

select coalesce(yourcolumn, 0)

There is lots of columns, I don't want to have to go through each one with something like ISNULL(FieldName,0) AS FieldName.

You can't always get what you want. Too bad. This is the way to go: column by column.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

Despite your reluctance to do so,

ISNULL(FieldName,0) AS FieldName

for each column is the correct way to handle this.

Upvotes: 1

sll
sll

Reputation: 62484

You have to manually specify each column in SELECT statement and use ISNULL() function:

SELECT ISNULL(columnName, 0), ... yes all of them

Upvotes: 3

Related Questions