How do I fetch the password from the database? Suppose I entered ABC as password; its hash is like $S$DNcgO9.CUWosQrms0/CqWvLIUFikmoczZ42n9bL6wxiH1OhIQbd5, which is stored in the user table.  I need to retrieve the plain password.

How can I achieve this?
The short answer is you cannot. Drupal is using a SHA-512 which is a cryptographic hash function. SHA-512 is one way which means it is not possible to get the original data back.

You can check this api. https://api.drupal.org/api/drupal/includes%21password.inc/function/user_check_password/7.x and this https://api.drupal.org/api/drupal/includes%21password.inc/function/user_hash_password/7.x.