1. TSQL IsNull Function
In SQL Server, in some cases we need to replace a null value with more descriptive value. For example, while we select a record from a table, we can replace the null value from a column into a more meaningful value. The TSQL IsNull function can do the trick for us.
2. TSQL IsNull Example
Now, have a look at the below query:
We use Microsoft supplied Pubs sample database to fetch the records from Titles table. In the result, at record numbers 7 & 10, the royalty columns are showing null entries. We can change the query to get a meaningful value for this column value as shown below:
- Here we use the
IsNullfunction to check the null column value. This function takes two parameters.
- For the first parameter, we supply the column name which must be checked with the null value. In our case, we supplied the Royalty column.
- Third parameter is the replacement value. IsNull function will check for Null value and when it finds the null, it will replace the null with this second parameter value.
- In our case, we have two null values for the Royalty column and the result here shows the values are rightly converted into a numeric 0 to denote no royalty for those books.
3. TSQL IsNull Function Restrictions
When using the IsNull function, the replacement value should match with the data type of the first column. Look at the example below:
- Data Type of the Royalty column is int and we pass this as first parameter to the
- Royalty Column and replacement values are not matching as the second argument is not an integer but a string.
- When we execute the query, SQL Server reports the error stating ‘No. RY’ is not integer type.
4. Code Reference
-- Shows Null Value
Select Title, Type, Royalty
-- Replace Null Value
Select Title, Type,
isNull(Royalty, 0) as Royalty
-- DataType Mismatch Error
IsNull(Royalty, 'NO. RY') Royalty
Categories: SQL Server
Do you like this Example? Please comment about it for others!!