sql server: can i insert a $ into a money field

Question

i am inserting values like '$5.99' (or trying to insert) into a money field. it doesnt like the dollars sign

i am actually doing a bulk insert from a csv file. one of the columns in the csv file has money in it with a dollar sign

can you please help me figure out how to do this bulk insert with the $ sign into a money field?

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (pricepaid).

how do i get rid of the sign?

Answer 1

No, you can't insert currency symbols into a money field.

It's really just a decimal(19,4) with a friendly name.

Answer 2

you can't, Money is just a numeric type slightly less flexible than decimal. to store with the symbol you would have to use a varchar

Answer 3

As a workaround, you could create a table with the same schema as your target, using a varchar column instead of money, and bulk insert into that. Then update the imported table to remove the '$' and copy the data to the original target.

Answer 4

Based on this question, as well as your earlier questions here and here, I'd recommend that you do your bulk insert into a temporary holding table, where you could define your "money" field as a varchar. Then write another SQL statement to move the data from the holding table to the real table. In this SQL statement, you could skip unwanted columns, remove the '$' from your "money" field and cast it as a money data type, etc.

Answer 5

Others have suggested a temporary table. If you use a temp table to hold the raw data from your CSV file, you should be able to convert directly to Money from a Varchar or Char field containing a $ character (that is, without removing it).

This worked for me in SQL 2008.