SQL Server: varchar to real

I was having trouble loading some data into a SQL Server database (MS). The particular problem was converting a type char to type real.

There are several ways to change data types. Typically, you'd use cast or convert functions. But everything I tried returned the same message: "Error converting data type varchar to real."

I googled to find out how other people dealt with this, but found nothing useful.

Then I tried something that worked: convert the type varchar to type money, then to type real. Easy.

Of course, this makes perfect sense if your numbers only run to two decimal places. In fact, it works to four decimal places (the same if you choose type money or smallmoney, except that the latter is stored in fewer bytes).

The options for retaining precision past four decimal places are somewhat messier. One way is to multiply the numbers by a few factors of ten, but you still have to pay attention to the range of viable values for money or smallmoney.

I'm not convinced that this issue is yet another demonstration of the unwieldiness of MS SQL Server. All databases have their pain points; this is just another one.