Switch Negative Sign from Back to Front of Value

Question

I have 5 fields in my SQL table that are currently VARCHAR fields. The data in these fields are money amounts, but the negative values have the sign at the end of the number, rather than the front.

Example: 4.56-

How can I change these fields from VARCHAR to MONEY and switch the negative sign to the front of the values in the process?

I have tried CAST([FIELD_NAME] AS MONEY) but that hasn't worked. I have gotten the following error from this attempt:

Cannot convert a char value to money. The char value has incorrect syntax.

Answer 1

Detect the minus sign; remove it; cast the positive result to MONEY; and negate it.

CASE WHEN [FIELD_NAME] LIKE '%-' 
     THEN -(cast(replace([FIELD_NAME],'-','') AS money)) 
     ELSE cast([FIELD_NAME] AS money) 
     END