Why does SQL Server sometimes error when converting a number into a string, but other times succeeds and returns an asterisk?
I don’t know.
The best (and logical) answer I could find online is from Robert Sheldon, who attributes it to poor error handling practices, “…before error handling got a more reputable foothold.”
This makes it important to check your results. I imagine that there’s somebody who relies upon this exact functionality, but it’s pretty weird.