Perl constants are really just (optimized) subs, and to do this directly you interpolate the result of calling the sub into the substitution string:

$stmt =~ s/${\&TABLE_NAME()}.//g;
[download]

Or use a $tmp var as tcf22 suggests.

(Update: don't forget to use quotemeta if needed:

$stmt =~ s/\Q${\&TABLE_NAME()}.\E//g
[download]

and I have a feeling you meant to backslash that .)