If we ignore all the issues associated with the fact that position and momentum are unbounded observables, then the main difference between normalizable kets and wavefunctions is simply this:
A normalizable ket ##|\alpha\rangle## is an element of a Hilbert space. A wavefunction ##\psi## is an element of the Hilbert space of square-integrable complex-valued functions on ##\mathbb R^3##.
So the only difference is that in the latter case, we say that the Hilbert space is specifically ##L^2(\mathbb R^3)##, and in the former case, we don't.
If I remember correctly, Sakurai uses the formula ##\psi_\alpha(x)=\langle x|\alpha\rangle## to define wavefunctions from kets. I remember doing some calculations involving this formula when I took a class based on that book, and at the time I felt like they explained a lot. (I don't feel that way anymore, because now I understand how hard it is to justify these steps). For example, if we already know that ##e^{-ipl}## translates states a distance ##l## (i.e. ##e^{-ipl}|x\rangle=|x+l\rangle##), then we can do stuff like this:
$$\psi_\alpha(x-l) =\langle x-l|\alpha\rangle=\langle x|e^{-ipl}|\alpha\rangle$$
If we divide both sides by ##-l## and take the limit ##l\to 0##, we get
$$\frac{d}{dx}\psi(x)=\langle x|ip|\alpha\rangle =i\langle x|p|\alpha\rangle.$$
In texts at the level of Sakurai, this is justified by using Taylor's formula to rewrite the left-hand side, and the power series definition of the exponential function to rewrite the right-hand side. The problem with that is that the power series definition only works when the exponent is a bounded operator. Anyway, if we still trust the result, we have "derived" that the p operator on the space of kets corresponds to ##-i\frac{d}{dx}## on the space of wavefunctions defined through the formula ##\psi_\alpha(x)=\langle x|\alpha\rangle##.
I'm not sure what to think of calculations like this, where we just pretend that the mathematical difficulties don't exist.
So what if we
don't ignore the fact that position and momentum are unbounded? For starters we can no longer say that the kets are elements of a Hilbert space. There's still a Hilbert space associated with the theory, but kets aren't elements of it. The proper way to define them involves choosing an appropriate subspace ##\Omega## of the Hilbert space, and then define bras as continuous linear functionals on ##\Omega## and kets as continuous antilinear functionals on ##\Omega##. The notation ##\langle x|\alpha\rangle## shouldn't be interpreted as an inner product. It should be interpreted as ##\langle x|\big(|\alpha\rangle\big)##, i.e. the value of ##\langle x|## at ##|\alpha\rangle##, where ##|\alpha## is an element of ##\Omega##. Now I think the formula ##\psi_\alpha(x)=\langle x|\alpha\rangle## actually makes sense, and I think the steps of the calculation above can be justified too. It's pretty far from trivial though.