When do quadratically integrable functions vanish at infinity?

In quantum mechanics we use quadratically integrable functions ($psi in L^2$).
This means
$$ int_{-infty}^infty |psi(x)|^2 mathrm{d}x < infty. $$

I’m interested in the question when those function vanish at infinity, i.e.
$$ lim_{x rightarrow pm infty} psi(x) = 0. $$

I know that this is not the case for every function in $L^2$, see for example this answer or this answer.

I found in a similar question something interesting:

Suppose $f : mathbf R to mathbf R$ is uniformly continuous, and $fin L^p$ for some $pgeq 1$. Then $|f(x)|to 0$ as $|x| to infty$.

Another interesting answer is this one.

My questions are:

1. How can one prove the given statement?
2. What are other cases where quadratically integrable functions vanish at infinity?
3. Which cases are relevant in physics (for quantum mechanics)?

---

Edit:

My first question was answered in the comments by @reuns.

My remaining question is:

What criteria (beside uniform continuity) do exist, so that quadratically integrable functions vanish (or not) at infinity?