Converting varchar to decimal 1

[Naith]

Hi,

I have currency values like "$ 45,001.45" etc stored as varchar. Because I need to do calculations on these fields, I've tried to convert this column to decimal:

convert(decimal(18,2),replace("bill_amt$",',',''))

However, there are a lot of records with null or '' values which I guess are interfering with this conversion, as I'm returning an "unable to convert" error.

Any suggestions how I might achieve this?

Naith

 

[Naith]

Oh, and I've already stripped the "$ " section of the string out. Forgot to mention that in the original post.

 

[vongrunt]

select convert(money, '$ 45,001.45')

... and then convert that to decimal if needed.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[Naith]

Good thinking. Thanks.