Menu
SELECT * from book;
how to convert NULL result to 0 from the sql ?
384
UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0. Cleaning data is important for analytics because messy data can lead to incorrect analysis.
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. Because the result of any arithmetic comparison with NULL is also NULL , you cannot obtain any meaningful results from such comparisons. In MySQL, 0 or NULL means false and anything else means true.
In SQL Server, NULL value indicates an unavailable or unassigned value. The value NULL does not equal zero (0), nor does it equal a space (' '). Because the NULL value cannot be equal or unequal to any value, you cannot perform any comparison on this value by using operators such as '=' or '<>'.
You need to use NULLIF() function from MySQL. The syntax is as follows: SELECT NULLIF(yourCoumnName,' ') as anyVariableName from yourTableName; In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.
You're looking for the COALESCE keyword:
SELECT COALESCE(fieldName, 0) FROM book
SELECT IFNULL(pages, 0) FROM book;
if pages was the name of your column.
39
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
