A smooth vector field on $S^2$ vanishing at a single point

Short Version:

There is no need to use "complicated" functions to kill the field at "infinity". Standard coordinate fields will do the trick, thanks to the stereographic coordinates structure.

Let $(u,v)$ be the stereographic projection coordinates on $\mathbb{S}^2-\{N\}$. Then consider the vector field $\partial_u$ (or also $\partial_v$). A priori, it is defined only on $\mathbb{S}^2-\{N\}$

Now try to compute the local expression of this vector field in stereographic coordinatates but from the south pole. You will notice that the field vanishes at zero (i.e. at the south pole $S$).

Therefore, you can extend the field $\partial_u$ to a well defined vector field on $\mathbb{S}^2$ which vanishes exactly at one point (i.e. the south pole $S$).

Long Version:

More precisely, let us denote $(u,v)$ the stereographic coordinates relative to the projection from the north pole, that is the map $\phi_N : \mathbb{S}^2-\{N\} \to \mathbb{R}^2$. Let us denote $(\overline{u},\overline{v})$ the stereographic coordinates relative to the projection from the south pole, which is the map $\phi_S : \mathbb{S}^2-\{S\} \to \mathbb{R}^2$.

Now, consider the vector field $\partial_u$ (in coordinates), defined on $\mathbb{S}^2-\{N\}$. On the intersection of the two charts, $\mathbb{S}^2-\{N,S\}$, we can compute the expression of $\partial_u$ in $(\overline{u},\overline{v})$ coordinates. The result is:

$\partial_u = (\overline{v}^2-\overline{u}^2)\partial_\overline{u} - 2\overline{u}\overline{v}\partial_\overline{v} \qquad (1)$

Where, of course, $(\overline{u},\overline{v}) = \phi_S \circ \phi_N^{-1} (u,v)$. You can easily see that this vector field can be extended at the north pole, by formula (1).

Therefore, a field with the property you required is:

$X_p = \begin{cases} \left(\phi_N^{-1}\right)_* \left(\frac{\partial}{\partial u}\right) & p \in \mathbb{S}^2-\{N\} \\ \left(\phi_S^{-1}\right)_* \left((\overline{v}^2-\overline{u}^2)\frac{\partial}{\partial \overline{u}} - 2\overline{u}\overline{v}\frac{\partial}{\partial \overline{v}}\right) & p \in \mathbb{S}^2-\{S\} \end{cases}$

$X_p$ is a well defined vector field on the whole $\mathbb{S}^2$. It is also obviousvly smooth, since it is smooth in local coordinates. Moreover $X_N =0$, and $X_p = \partial_u \neq 0$ on $\mathbb{S}-\{N\}$, as required.

Explicit calculation for the change of coordinates

The change of coordinates map (and its inverse) can be computed explicitly:

$(u,v) = \phi_N \circ \phi_S^{-1}(\overline{u},\overline{v}) = \frac{(\overline{u},\overline{v})}{\overline{u}^2+\overline{v}^2}$

$(\overline{u},\overline{v}) = \phi_S \circ \phi_N^{-1}(u,v) = \frac{(u,v)}{u^2+v^2}$

By using these explicit expression it's easy to express $\partial_u$ in terms of barred coordinates:

$\frac{\partial}{\partial u} = \frac{\partial \overline{u}}{\partial u}\frac{\partial}{\partial \overline{u}} + \frac{\partial \overline{v}}{\partial u}\frac{\partial}{\partial \overline{v}}$

Now, I leave to you the last step (i.e. to explicitly compute the derivatives). REMEMBER, when computing the derivatives of the barred coordinates with respect to $u$, to express them in terms of BARRED coordinates.

Let $N$ be the northern pole in the sphere $S^2$. The planes passing through $N$ and perpendicular to the $yz$ plane cut the sphere in a circle. Consider the field $Y$ of unit vectors tangent to those circles: this is defined in the complement of $N$. Now multiply $Y$ by the function $1-z$, to obtained a field $X$. Now notice that $X$ extends to the whole of $S^2$, and vanishes only at $N$.

The Sphere $S^n$ has an atlas with two charts $(U_1=S^n\setminus\{N\},\phi_1)$ and $(U_2=S^n\setminus\{S\},\phi_2)$, where $\phi_1$ and $\phi_2$ are the stereographic projections respectively from the north and the south pole.
The transition map $\phi_2\circ\phi_1^{-1}:\phi_1(S^n\setminus\{S,N\})=\mathbb{R}^n\setminus\{0\}\to\phi_2(S^n\setminus\{S,N\})=\mathbb{R}^n\setminus\{0\}$ is given by the inversion $\rho$ through the unit sphere in $\mathbb{R}^n$, i.e. $\phi_2\circ\phi_1^{-1}(x)=\rho(x)=|x|^{-2}x$.

Consequently the tangent space $TS^n$ has an atlas consisting of the two charts $(U_1\times\mathbb{R}^n,T\phi_1)$ and $(U_2\times\mathbb{R}^n,T\phi_2)$, and the transition map $(T\phi_1)\circ(T\phi_1)^{-1}$ is $T\rho$.

So the vector field $v$ on $S^n$ is given a ordered pair of maps $v_1,v_2:\mathbb{R}^n\to\mathbb{R}^n$ such that $v_2(\rho(x))=(T_x\rho)v_1(x)$ for all $x\in\mathbb{R}^n\setminus\{0\}$.

What you want is a smooth map $v_1:\mathbb{R}^n\to\mathbb{R}^n\setminus\{0\}$ such that:

1. $x\in\mathbb{R}^n\setminus\{0\}\to (T_{\rho(x)}\rho)v_1(\rho(x))\in\mathbb{R}\setminus\{0\}$ extends to a smooth map defined on all $\mathbb{R}^n$ and vanishing at $0$.

Let us take $v_1(x)=f(|x|)v^0$ where $v^0$ is a nonzero vector and $f:\mathbb{R}\to\mathbb{R}_+$ a smooth function. The condition (1) becomes:

1. $x\in\mathbb{R}_+\to f(x^{-1})(T_{\rho(x)}\rho)v^0)\in\mathbb{R}\setminus\{0\}$ extends to a smooth map vanishing at $0\in\mathbb{R}$.}$

Because $x\in\mathbb{R}_+\to x^{-1}\in\mathbb{R}_+$ together with all its derivatives diverges polynomially as $x$ goes to $0$ it is necessary for us that $f\in\mathcal{S}(\mathbb{R})$, i.e. $f$ rapidly decreases at infinity. For example we could take $f(x)=e^{-x^2}$

Edit after Luca's Answer The introduction of $f$ is redundant.
By the definition $\rho(x)=|x|^{-2}x$ we get the components of $T_{\rho(x)}\rho\equiv(D\rho)(\rho(x))$ are homogeneous polynomial of second degree in the componens of $x$.

So the condition 1 is satisfied in particular taking $v^0$ constant non zero as in the answer of Luca.