Problem and its Solution in SQL Query: How to solve If database table field is blank or null then replace or fill it with zero (0) or one (1) without using if condition in query||Solution procedure of if field = blank or Null then field =0 or 1
March 24, 2011 1 Comment
Let us assume we have a table called “ITEM” and it has four fields named “ order_no”, “item_no”, “qty” and “style_sample_no” . In the field “style_sample_no” there is no data i.e. this filed is blank/null or empty.
Check the image (“style_sample_no” is empty)
So, if you want to implement the condition “ If style_sample_no==null then replace style_sample_no with 0 i.e. style_sample_no=0 ” you can do it easily with SQL Query without any IF Condition.
Here is the Solution (Check the image below)
By using COALESCE(i.style_sample_no,0) you can easily replace the blank field of style_sample_no of ITEM table with 0’s or “1”s without using any IF condition in the SQL query.
select i.order_no,i.item_no,i.qty, COALESCE(i.style_sample_no,0)as sample from item i
How To : Postgre SQL Issues about using COALESCE function