How do I return a default value when a column value is NULL in SQL?
-
Without context, it is difficult to provide useful suggestions. And I think you confuse "default" value with "null value". Inserting a NULL value as a column in a row will not somehow provoke the db engine to replace that NULL with the default assigned to that column. If your question is based on code in an application that inserts into a table and you want to refresh what is displayed, then you need to provide that context for others.SMor– SMor2019-06-08 14:05:26 +00:00Commented Jun 8, 2019 at 14:05
-
You can set default value while you are designed your table. Default value automatically be shown when there is null valueMd Farid Uddin Kiron– Md Farid Uddin Kiron2019-06-08 14:09:04 +00:00Commented Jun 8, 2019 at 14:09
-
Check stackoverflow.com/questions/3817885/…Serg– Serg2019-06-08 14:13:23 +00:00Commented Jun 8, 2019 at 14:13
-
@MdFaridUddinKiron No default will be inserted into the column when INSERT command doesn't provide the value for the column explicitly.Serg– Serg2019-06-08 14:14:58 +00:00Commented Jun 8, 2019 at 14:14
-
spaghettidba.com/2015/04/24/…MJH– MJH2019-06-08 15:25:06 +00:00Commented Jun 8, 2019 at 15:25
|
Show 1 more comment
4 Answers
You can use COALESCE. Please refer https://www.sqlshack.com/using-the-sql-coalesce-function-in-sql-server/ for more info.
SELECT COALESCE (NULL,'A')
Comments
IsNull is used when the value is null and you want a default value to return.
Select IsNull(YourColumn, defaultvalue)
i.e.
Select IsNull( CandidateName , ''), -This is string column and return default value ''
IsNull(CandidateSalaryExpected, 0), --This is Integer/Double/Numeric column and return default value 0
IsNull(MaritalStatus , 0) --0 - marital means unmarried, 1 means married. This is boolean value
You can use Coalesce value too. Check the below link.
Comments
Seems you are trying to fulfill your column with default value when any NULL value found.
You could try this:
SELECT COALESCE([dbo].YourTableName.[YourColumnName],'Default Value When Null Encountered') AS YourTableAlias FROM [dbo].[YourTable]
Test With Real Sample:
See my Table Column below where some FlatName is NULL
Real Query For Default Value When NULL Found:
SELECT FlatId,COALESCE([dbo].[Flat].[FlatName],'Default Value When Null Encountered') AS FlatName, FlatDescription FROM [dbo].[Flat]
Output Of Query:
answered Jun 9, 2019 at 13:57
Md Farid Uddin Kiron
22.9k3 gold badges29 silver badges54 bronze badges
Comments
Instead of returning default value, you can design your table to add default value if the value is not provided. See below example
CREATE TABLE DemoTable
(
ID INT,
Name VARCHAR(100),
RecordAddedOn DATETIME DEFAULT GETDATE()
)
You cane take a look at below link to have a better idea about default constraint. Default Constraint
However, if you want to return default value, you can use ISNULL(ColumnName, ‘Default Value’)
