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,
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
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...)
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.