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.
ISNULL Function in SQL Server To use this function, all you need to do is pass the column name in the first parameter and in the second parameter pass the value with which you want to replace the null value. So, now all the null values are replaced with No Name in the Name column.
Use IFNULL or COALESCE() function in order to convert MySQL NULL to 0. Insert some records in the table using insert command. Display all records from the table using select statement.
Using the SQL COALESCE function, it is easy to replace missing or NULL values in SELECT statements. Specific values can be set directly with COALESCE and the mean, median or mode can be used by combining COALESCE with WINDOW functions.
The ISNULL() function returns a specified value if the expression is NULL. If the expression is NOT NULL, this function returns the expression.
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..
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With