To demonstrate how to get a default value for a nullable fields in SQL Server, we have taken below MyDetails table as an example.
Above table has following records
Now, let’s assume that we have to get not null value of either FullName, City or if both columns have null value then a Default value then we can use COALESCE
function.
SELECT FullName, City, COALESCE(FullName, City, 'Default City') DefaultCity FROM MyDetails
In the above code snippet, notice the COALESCE
function. The first parameter is FullName, second parameter is City and third parameter is the hard coded value “Default City”.
In above case, non null value of FullName and City will be taken as the value of [Default City] column. In case both FullName and City is null then “Default City” value is set as the value of [Default City] column.
Views: 7785 | Post Order: 63