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.

回答1:

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

回答2:

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!

回答3:

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

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

回答4:

Despite your reluctance to do so,

ISNULL(FieldName,0) AS FieldName

for each column is the correct way to handle this.

回答5:

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.

回答6:

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.

回答7:

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.

回答8:

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.

回答9:

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