SQL query that replaces null values.

Question

I need an 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

• User ID
• 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 the SQL DateTime format chart for the CONVERT statement above.

Answer 2

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

Make sure that you are having trouble casting.

For instance:

--(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 3

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

ISNULL .

Answer 4

Oracle nvl. - SELECT nvl(col_name, desired_value) FROM foo.

decode, , ( "" "" - ).

Answer 5

IIF - , .

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

IIF , .
SQL:     IIF (, 1, )

Answer 6

. , , .

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

Repeat for each column.