Which function can be used to replace null value with default value? select IFNULL(country, 'USA') as country from customer select ISNULL(country, 'USA') as country from customer select NVL(country, 'USA') as country from customer select REPLACENULL(country, 'USA') as country from customer
Answer (1)
To handle NULL values in databases, different functions are used depending on the SQL database management system (DBMS) being employed.
Here is an overview of each option mentioned in your question:
IFNULL(country, 'USA') : This function is used in MySQL. It replaces NULL values in the specified column with the default value provided, which in this case is 'USA'.
ISNULL(country, 'USA') : This function is used in SQL Server. Similar to the MySQL IFNULL function, it replaces NULL values with a specified default value.
NVL(country, 'USA') : This function is used in Oracle databases. It substitutes a default value for NULL values in the specified column.
REPLACENULL(country, 'USA') : This function is not a standard SQL function recognized in popular database systems.
Therefore, the correct approach to replace NULL values depends on the SQL database system being used. Here’s a quick summary based on each DBMS:
For MySQL , use IFNULL(country, 'USA').
For SQL Server , use ISNULL(country, 'USA').
For Oracle , use NVL(country, 'USA').
It is essential to use the function that is appropriate for the database system you are working with. This ensures that code is both functional and compliant with the DBMS being used.
