Convert varchar to float, decimal

rohanpdsouza · #1 (**permalink**) 01-10-12, 00:15

Hello,

I have a field in my database that is stored as varchar. The values are usually contain a decimal, and should have really been a float or decimal. In order for me to do analytics in my BI environment, I need to convert this to a float or decimal.
eg of values.
10.00
20.00
0.00
15.00
or could be missing

when I use cast(value as float) or cast(value as decimal(9,2)) or convert(float, value) I get an error

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

any help will be greatly appreciated.

Thanks,

Wim · #2 (**permalink**) 01-10-12, 04:31

Try this:

Code:

SELECT id, aColumn, 
	CASE WHEN ISNUMERIC(aColumn) = 1
		THEN CAST(aColumn AS FLOAT) 
		ELSE NULL 
	END as aFloat
FROM #DaTable

MCrowley · #3 (**permalink**) 01-10-12, 10:39

You probably have a few rogue characters running around. Here is an example of how the ISNUMERIC function can trip you up...

Code:

select convert(float, '$10.00')
select isnumeric ('$10.00')

ISNUMERIC will return true on a value that can be converted to some numeric format, which includes money, and scientific notation (I'm looking at you, Excel).

Internet Services

Web Development

Digital Marketing

Consumer Tech