Is there a simple way to characterize the functions in $C^\infty((0,1])\cap L^2((0,1])$?
That is, given a function $f(t)\in C^\infty((0,1])$, is there a necessary/sufficient condition I can check to see if it's square integrable? An example of such a function is $f(t)=t^{-1/3}$, which diverges as $t\to0$ but satisfies $\int_0^1 f(t)^2\,dt=3<\infty.$
Notes: I was hoping to prove something to the effect that $f(t)$ is square integrable if and only if $$\lim_{t\to0} \frac{f(t)^2}{t^p}=L < \infty$$ for some $p>-1$. This is certainily a sufficient condition by the "limit comparison test" for improper integrals, but I'm not sure if it's necessary. (But, I also couldn't find a simple counterexample!)
Not a full answer to your question, but a counterexample to your conjecture: consider $$f(t) = \frac{1}{t} \left[\sin\left(\frac{2\pi}{t}\right)\right]^{2^{1/t}}.$$ The intuition here is that the first $1/t$ factor ensures that $f$ will violate your limit test, while the sine term will make the function look like a sequence of sharper and sharper spikes whose areas decrease even as their amplitudes diverge. (Surely an example is possible where the sine exponent grows less aggressively, but this is the easiest one for which I could prove square-integrability.) Here is a plot of $f(t)^2$:
To see $f(t)$ is square-integrable, we can estimate $$\int_{1/(n+1)}^{1/n} f(t)^2\,dt < (n+1)^2 \int_0^{1/n} \sin^{2^{n+1}}(2\pi n x)\,dx = \binom{2\cdot 2^n}{2^n}\frac{(n+1)^2}{n 4^{2^n}} < \frac{(n+1)^2}{2n \cdot 2^{n/2}}.$$