The big difference here is "Implicite" and "Explicite" Conversion. The first return is an implicite conversion of the arguments. Both values will convert to a number value and compare. The second return is an explicite conversion and the arguments are converted to VARCHAR or NVARCAHR and compared. The third return is explicite but does not care about anything from the second % SIGN on.

RedSpear