SQL Server how to set a default value when the column is null

Question

This is my sql in SQL server:

SELECT user_Name ,user_pass ,user_hometown FROM user_baseinfo   

Sometimes the column can be null. When it is, I want to replace it with a default value.

Answer 1

SELECT
    ISNULL(user_Name, 'Unknown Name') AS user_Name,
    ISNULL(user_pass, 'Unknown Pass') AS user_pass,
    ISNULL(user_hometown, 'Unknown Hometown') AS user_hometown
FROM user_baseinfo

---

Be aware though that ISNULL has precedence of the first argument type. This may cause undesired cutting of the default value, if column in the first argument has type, which is shorter than that of the default value. In such cases COALESCE is the better choice.

Below is an example showing the difference between ISNULL and COALESCE:

CREATE TABLE #Sample (Value1 varchar(100), Value2 char(3));

INSERT INTO #Sample (Value1, Value2)
VALUES
    ('AAA', 'AAA'),
    ('BBB', 'BBB'),
    ('CCC', 'CCC'),
    (NULL, NULL);

SELECT
    ISNULL(Value1, 'Default') AS Value1,
    ISNULL(Value2, 'Default') AS Value2
FROM #Sample;

SELECT
    COALESCE(Value1, 'Default') AS Value1,
    COALESCE(Value2, 'Default') AS Value2
FROM #Sample;

See that Default cuts down to Def when ISNULL is used.

Answer 2

Use case

select case 
          when user_Name is null then "default value" 
          else user_name 
       end  
from table

Answer 3

For existing rows (Assuming column is varchar)

ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT N'Default Value' FOR ColumnName;