Square integrable function that doesn't go to zero?

As David and proximal have pointed out in the comments, there are many square-integrable functions which do not go to $0$ in the limit, even continuous ones. However, we do have the following:

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

Consider the function: $$ x^2\exp(−x^8 \sin^2 x) $$

I found it in this article: http://arxiv.org/abs/quant-ph/9907069. More specifically on page 7.

Along the lines of David Mitra's comment, try $$f = \sum_{n=1}^\infty 1_{[n, n + 2^{-n}]}.$$ That is, $f(x) = 1$ if $x$ is in one of the intervals $[1,1+1/2], [2, 2+1/4], [3, 3+1/8], \dots$, and $f(x) = 0$ otherwise. Clearly $f(x)$ does not approach 0 as $x \to \infty$, since for any $N$ there are $x \ge N$ with $f(x) = 1$ and other $x \ge N$ with $f(x) = 0$. But on the other hand, it's easy to compute that $\int f^2 = \sum_{n=1}^\infty 2^{-n} = 1$.

With a little more work you can make this function continuous, or even $C^\infty$.