Can you convert or cast a NVARCHAR data type column to money data type?

Question

question

Slick84 asked Feb 22, '11

Can you convert or cast a NVARCHAR data type column to money data type?

Hi, I'm trying to `CAST(Column AS MONEY)` where the original data type is `NVARCHAR`. I keep getting this error. Msg 235, Level 16, State 0, Line 1 Cannot convert a char value to money. The char value has incorrect syntax. Any idea's? I looked at MSDN and it says it should be an implicit conversion so why the error? I've got 150k rows that I'm selecting out of and one value I found in there is 'GL16B'. Could this be the culprit?
Overview: The problem is that I'm importing a CSV file using BCP in a table where I've assigned all columns to NVARCHAR(255) because BCP kept breaking. In the CSV, some one uses comma's to seperate data, such as CHARLES E, WALKER, JR which BCP treats like three columns. I have only two columns defined for the fname and lname. What this does is it pushes every column after lname one place to the right. Which is why I have that 'GL16B' value in the NVARCHAR column which should only have MONEY values. Please help. Thanks,
S

tsql

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Feb 22, 2011 at 02:46 PM

Could this be the culprit? Yes, i.e.

declare @n nvarchar(255);
set @n = N'GL16B';
select cast(@n as money);

produces the error just like you describe. Is there any way to change the delimiter in the CSV file from comma to something else, like the pipe character?

5 ·

Slick84 commented · Feb 23, 2011 at 07:57 AM

Changed the delimiter to PIPE and regenerating file now. Thanks Oleg.

0 ·

dvroman commented · Feb 23, 2011 at 01:46 PM

I've found that Excel does a better job of interpreting the CSV correctly than SQL. Therefore, I use Excel on the CSV to convert it to XLS and import that file. Many less errors and delimiters are handled correctly.

0 ·

Answer 1 · 2011-02-22T14:54:34Z

Kevin Feasel answered Feb 22, '11

declare @MoneyValue nvarchar(10); set @MoneyValue = '250000.00'; select CAST(@MoneyValue as money); Yeah, there shouldn't be a problem casting an nvarchar as money...unless it's not really a money value, in which case you get that error. To find your bad values, you can do something like the following, which helps if you also have a row number or some primary key: with records as ( select Id, PATINDEX('%[^0-9.,]%', VALUETOCONVERT) as ix from YOURTABLE ) select * from records where ix > 0 This won't solve your problems directly, but at least it will let you see which columns have problems and see what kind of clean-up work you'll have to do. If it turns out that surname and suffix issues are responsible for all of your problems, you could also create an update script to fix these problems, like with records as ( select Id, PATINDEX('%[^0-9.,]%', VALUETOCONVERT) as ix from YOURTABLE) update t set FirstName = LastName, LastName = NextValue, ..., PreviousValue = Money, Money = FollowingValue, FollowingValue = ... from YOURTABLE t inner join records r on r.Id = t.Id where r.ix > 0 Hopefully this helps you get a handle of the nature and scope of the problem. Another thing, if you have the ability to do so and you need to do this kind of process more than once, might be to re-create the text file and at least put some kind of text identifier like a quotation mark, so you could have Charles E, "Walker, JR", ... SSIS can handle this kind of text qualifier, but it looks like [bcp doesn't have a direct comparison and you'd need to create a format file]( http://jessesql.blogspot.com/2010/05/bulk-insert-csv-with-text-qualifiers.html), so to take advantage of this, you'd have to use SSIS to load the staging table. Otherwise, maybe use a different delimiter. We use ~ for our delimiter, for example. Oleg's | is another good example (though they don't like it on UNIX servers...)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2011-02-22T14:41:29Z

Magnus Ahlkvist answered Feb 22, '11

If the file is comma-separated, an unescaped comma-value in a column would break things. Is the text comma-separated, fix width etc? If the data is comma-separated, you should do fine using an .fmt-file to specify the file layout for BCP. If you don't have a Money-value in a column, you can't cast that column to Money. Please be a little more specific about your text-file-layout.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question