Set a value when the column is null ~ Beginner School

Set a value when the column is null

In sql server 2008 there are two functions to replace NULL values with another values

1. ISNULL function required two parameters: the value to check and the replacement for null values

ISNULL(value,replacement)

2.COALESCE function works a bit different COALESCE will take any number of parameters and return the first non-NULL value , I prefer COALESCE over ISNULL 'cause meets ANSI standarts, while ISNULL does not.

COALESCE(value1,value1,value3, valueN,replacement)

Solution 1:

SELECT
  Name, DOB,
  (CASE WHEN Address1 IS NULL THEN 'NA' ELSE Address1 END) AS Address1,
  (CASE WHEN Address2 IS NULL THEN 'NA' ELSE Address2 END) AS Address2,
  ...
FROM Users

Solution 2:

Use isnull:

SELECT
    Name, 
    DOB, 
    isnull(Address1, 'NA') as [Address1], 
    isnull(Address2, 'NA') as [Address2], 
    isnull(City, 'NA') as [City], 
    isnull(State, 'NA') as [State], 
    isnull(Zip, 'NA') as [Zip]
FROM Users

You can also use coalesce, which is exactly like isnull except that it can take more than two arguments. The arguments are checked from left to right and the first non-null value is returned. Values must be of the same type.

SELECT 
  Name, DOB, Address1, 
  coalesce(Address2,'NA'), coalesce(City,'NA'), 
  coalesce(State,'NA'), coalesce(Zip,'NA')
FROM Users

About Admin

Beginner School is optimized for learning. This is useful for getting basic knowledge with easiest way. Tutorials, examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. With the help of other reference book and website, the motive to provide you easiest way to understand the coding, technology and other things.....

Beginner School