Converting a string to Numeric in SQL

I have run into this again and had to spend time compiling the answer, so I am posting it for future use.

I needed to convert a string to numeric in SQL.  Seems pretty straightforward, right?

select convert(numeric(19,5),'0.0')

So what happens with my string looks like this?

select convert(numeric(19,5),'abc0.0')

Error converting data type varchar to numeric.
Ok, so I need to make sure it is a number first.

select case isnumeric('abc0.0') when 1 then convert(numeric(19,5),'abc0.0') else 0 end

All done, right?  Not so fast.  All of the following return 1 (true)
select isnumeric('$0.0')

select isnumeric('123,000.0')

But the following result in an error:
select convert(numeric(19,5),'$0.0')

select convert(numeric(19,5),'123,000.0')

So we need to make some basic decisions about what we consider to be valid in our numeric value and replace out those offending characters before evaluating it.

select case isnumeric(replace(replace(replace(replace('123,000.0',',',''),'$',''),'+',''),'-','')) when 1 then convert(numeric(19,5),replace(replace(replace(replace('123,000.0',',',''),'$',''),'+',''),'-','')) else 0 end



Did this help you?  If so, please leave a comment!