here in this query I want to replace the values in Person.Contact of Adventureworks database with some new values. The below query case statement is working fine for other values but I am not able to change the values those are in NULL. I am using SQL Server. Any help is appreciated.
select contactid,Title,FirstName,MiddleName, case MiddleName when 'R.' then 'Robert' when 'B.' then 'Bids' when 'J.' then 'John' when is null then 'New Name' else 'No Name' end, LastName from Person.Contact
case
when MiddleName is null then ...
when MiddleName = 'R' then ...
end
I'd use the ISNULL function - it will return the value given if the field is NULL:
select contactid,Title,FirstName,MiddleName,
case ISNULL(MiddleName, 'NULLVALUE')
when 'R.' then 'Robert'
when 'B.' then 'Bids'
when 'J.' then 'John'
when 'NULLVALUE' then 'New Name'
else 'No Name'
end, LastName from Person.Contact
Sorry to post 7 years later, but I've been trying to find a solution for Interbase / Firebird and this post kept popping up. None of the solutions here work because there is no ISNULL, so I figured I'd help anyone else who might come here looking for that:
select contactid,Title,FirstName,MiddleName,
case COALESCE(MiddleName, 'NULLVALUE')
when 'R.' then 'Robert'
when 'B.' then 'Bids'
when 'J.' then 'John'
when 'NULLVALUE' then 'New Name'
else 'No Name'
end, LastName from Person.Contact
