ISNULL in Sql Server is uded to check and return alternate value if value is NULL, while to only check the NULL value, we can us IS NULL with space, in MySQL and Sqlite, for same functionality, we can be use IFNULL and in Oracle we can use NVL.
Suppose we want to return 0 if column value is null then we can use
select ISNULL(columnName, 0) from table_name
Column type and alternate return type should be same, say the column type is INT then alternate value must also be INT, it cannot be string. Let say we have following query
Select emp_id, first_name + ' ' + last_name as [full_name]
salary, bonus, salary + bonus [total_salary]
From employee
Suppose last is null and discount is null then what would be the value for fullname and totalsalary column, any guess, they will be null, so to fix the issue we can use ISNULL and our query will be
Select emp_id, first_name + ' ' + ISNULL(last_name) as [full_name]
salary, bonus, salary + ISNULL(bonus, 0) [total_salary]
From employee
Take another example where you need to calculate percentage and divisor may be NULL then the result will be null Select col1 * col2 / col3 as [result] From table In this case if col3 is NULL then result will be NULL while it should be col1 * cul2, so fix this issue we can check if col3 is NULL then it must be divided by 1, and it can be achieved by using ISNULL function in SQL Server
Select col1 * col2 / ISNULL(col3, 1) as [result] From table
IS MySQL and Sqlite we can use IFNULL, like this
Select emp_id, first_name + ' ' + IFNULL(last_name) as [full_name]
salary, bonus, salary + IFNULL(bonus, 0) [total_salary]
Srom employee
And finally in Oracle we can use NVL, like this
Select emp_id, first_name + ' ' + NVL(last_name) as [full_name]
salary, bonus, salary + NVL(bonus, 0) [total_salary]
From employee
Let’s see how we can only check null value in a column, say we want to see all those employee where last name is NULL and bonus is NULL
select * from employee
where last_name IS NULL
and bonus IS NULL
It will return only those records where last name is NULL and bonus is NULL.
|
Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
|
| By Hamden On 31 Aug, 13 Viewed: 3,140 |
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 1,084
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 924
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,311
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 666
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 12,556