Where in the table is it stored that /drupal/foo is actually /node/47 in order to mass query to remove /drupal and just leave the /foo part?
The table you want is {url_alias}.  The {url_alias.source}column is the internal path (eg node/123), the {url_alias.alias} is the alias itself.

Mucking with the database directly isn't recommended, though.  Your best bet is to use Pathauto and Globalredirect.  The first will allow you to set up patterns, the second will redirect nodes to the alias.

You then use the Delete Aliases (admin/config/search/path/delete_bulk) link and then Bulk Update (admin/config/search/path/update_bulk) to make the new ones.

If you must do this manually, I suggest looking into the what the Path include provides as an API, and using that.

Also be sure that you don't actually have a problem with a $base_url being set incorrectly in your settings.php