A SQL query that replaces null values.

Question

I need a SQL query that returns ContactDate, SortName, City, ContactType, and Summary from the tables below. If any value is null, I need it to return the text “No Entry”.

ContactTable

• ContactID
• ContactDate
• UserID
• Summary
• ContactType
• SortName

UserTable

• UserID
• FirstName
• LastName
• AddressID

AddressTable

• AddressID
• City
• Street
• State
• Zip

Answer 1

SELECT COALESCE(CAST(CONVERT(VARCHAR(10), ContactTable.ContactDate, 101) AS VARCHAR(10)), 'No Entry') AS ContactDate,
       COALESCE(ContactTable.SortName, 'No Entry') AS SortName,
       COALESCE(AddressTable.City, 'No Entry') AS City,
       COALESCE(ContactTable.ContactType, 'No Entry') AS ContactType
FROM ContactTable
LEFT OUTER JOIN UserTable ON ContactTable.UserID = UserTable.UserID
LEFT OUTER JOIN AddressTable ON UserTable.AddressID = AddressTable.AddressID

Here is a chart of SQL DateTime formats for the CONVERT statement above.

Answer 2

SELECT 
  ISNULL(ContactDate, 'No Entry') AS ContactDate
FROM Table

Using ISNULL is pretty simple.

Answer 3

COALESCE() on any platform that is worth its weight in salt.

Make sure to handle casting issues.

Such as:

--(SQL Server)
SELECT
  C.ContactID,
  COALESCE(CAST(CONVERT(varchar(10), C.ContactDate, 101) AS varchar(10), 'No Entry') AS ContactDate,
  COALESCE(SorName, 'No Entry') AS SortName

etc., etc.

Answer 4

Using 'IIF' is an Access DB solution but may work in other DBs.

SELECT IIF(IsNull(Foo), 'No Entry' ,Foo), IIF(IsNull(Bar), 'No Entry' ,Bar) From TableName

The function IIF returns one of 2 values depends of the evaluation of an expression.
SQL Syntax: IIF( expression, true-value1, false-value )

Answer 5

The Oracle version of this function is called nvl. Same usage -- SELECT nvl(col_name, desired_value) FROM foo.

The more general version of this is decode, which has three parameters and allows you to specify which column value you want to perform a replacement for (so you can replace all 'Johnny' with 'John' or something).

Answer 6

You can also make different calls for each column. It will take more individual calls, but it may be faster if you don't have many rows to update.

update ContactTable
set ContactDate = 'No Entry'
where ContactDate is null;

Repeat for each column.