Search code examples
sql-serversyntaxdecimalcurrencyvarchar

How to copy varchar column with currency value to decimal in SQL Server?

I have a table with around 20K rows of data. One varchar column contains currency info in the format $xxx,xxx,xxx.00. I'd like to create an additional column that is of type decimal and copy the varchar data into it, obviously while stripping off the $ and ,'s.

I've played with cast and convert, mixed with trim and replace functions, but haven't been successful yet in getting this into a new column.

Because we're doing many future calculations on the data, we want it converted once instead of having to convert it for each calculation.

Any suggestions would be greatly appreciated!

Thank you in advance!

--Kent

Solution

  • Try replacing the $ and the ',' and then Convert. You should be Good

    CONVERT(NUMERIC(28,4),REPLACE(REPLACE(stringColumn,'$',''),',',''))

    SQL Fiddle here.