0

How to return a default value when a column value is null in SQL

user11618466
  • 41
  • 1
  • 1
  • 2
  • 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 Jun 08 '19 at 14:05
  • You can set default value while you are designed your table. Default value automatically be shown when there is null value – Md Farid Uddin Kiron Jun 08 '19 at 14:09
  • Check https://stackoverflow.com/questions/3817885/sql-server-find-out-default-value-of-a-column-with-a-query – Serg Jun 08 '19 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 Jun 08 '19 at 14:14
  • https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – MJH Jun 08 '19 at 15:25
  • @user11618466 take a look and try with your table value. Hope it will help. If you have any more query let me know. Thanks and happy coding. – Md Farid Uddin Kiron Jun 09 '19 at 14:05

4 Answers4

6

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')
user11618478
  • 61
  • 1
3

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.

SQL Select Return Default Value If Null

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
1

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

enter image description here

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:

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
Md Farid Uddin Kiron
  • 16,817
  • 3
  • 17
  • 43
0

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’)

Mova
  • 928
  • 1
  • 6
  • 23