Error converting datatype vchar to numeric

[geoffcodd]

Hi,

I have the following SQL

INSERT INTO dbo.tblC0200_nSAP_FI_Line_Items_Cleaned
                      (CoCd, [Year/month], DocumentNo, Itm, [Amount in doc], Curr, [Amount in local cur], LCurr, [Amount in loc curr 2], LCur2)
SELECT     CoCd, [Year/month], DocumentNo, Itm, CONVERT(decimal(18, 2), [Amount in doc]) AS [Amount in doc], Curr, CONVERT(decimal(18, 2), 
                      [Amount in local cur]) AS [Amount in local cur], LCurr, CONVERT(decimal(18, 2), [Amount in loc curr 2]) AS [Amount in loc curr 2], LCur2
FROM         dbo.tblB0100_nSAP_FI_Line_Items_Raw_Import

it doesn't matter what I try but I still get the same error, the data looks like this

CoCd	Year/month	DocumentNo	Itm	Amount in doc	Curr	Amount in local cur	LCurr	Amount in loc curr 2	LCur2
2350	2009/06	ID19000860	  2	-1,000.00	EUR	-1,000.00	EUR	-1,380.30	USD

Any help anyone can give to put me in the right direction will be greatly appreciated

Thanks
Geoff

 

[SQL_Hell]

Hi there

You need to replace the apostrophe

select convert(decimal(18,2),replace('-1,380.30',',',''))

 

[geoffcodd]

Thanks for the reply, I have files of 200k+ rows what is the quickest way of stripping out the commas, Thanks

 

[geoffcodd]

Sorry it's ok was being a muppet thanks for your help

 

[SQL_Hell]

Not sure why I called them apostrophes they are indeed commas.

I dont think there is specifically a quick way of doing this, standard update query will surfice.