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

sql query

Have you ever faced a problem while using SQL Query in your software where you have to replace the blank or null field in database table with “0”  or  “1”  but  you are  unable to use “If condition” in the sql?


Here I have got a solution where you can replace  or fill the blank or null table data with “0” or “1” by using COALESCE   in SQL query.
 

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)
 
 

 table field is blank

 
 
 

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)
 

 sql query with zero

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

 

One Response to 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

  1. Daniel says:

    COALESCE – it’s just alias to CASE, and CASE is alias for IF ELSE-IF ELSE so implicit you still use in conditional flows 🙂 The goal is do not use in flows, coz’ it reduce performance.

    if you just need {0,1} or {true, false} try compare to NULL – it’s comparison and not conditional flows, i.e:

    SELECT *,
    aColumn is NULL — here no flows , i.e. IF (cond) so do this and this, ELSE do this
    FROM aTable;

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: