Assign default value to a datacolumn in a select statement.

Question

Hi,

I want to assign default values to a column in my select sql query so that if the value of that column is null I get that default value in my recordset. Is there anyway to do this?

Example:

select col1 (some default value) from tblname;

Answer 1

select
  isnull(col1, defaultvalue)
from
  tblname;

Answer 2

if you are using SqlServer you can use the CASE statement

example:

select case col1 when null then defaultval else col1 end from tblname

where defaultval is the default value. the data type of defaultval must be the same as that of col1.

Answer 3

The preferable way is to use ANSI compatible function COALESCE:

SELECT COALESCE(column_name, default_value) FROM table_name;

You also could read an article which compares COALESCE and ISNULL.

Answer 4

link text