Menu
I have a field value productlength of 0.123. This is from a view and has a data type of varchar.
I need to convert it to a float or numeric value so as o perform math comparisons.
convert(float,productlength) and cast(productlength as float) both do not work.
error varchar cant be converted to float or somethiing liek that.
From what I have read varchar can simply not be converted to a numeric string?
Any clever ways around this?
366
The exact reason for getting the error message in this case is that you are using the comma (,) as a decimal point and also the dots as group digit symbols. Though SQL Server considers as a decimal point the dot (.). Also when converting a varchar to float you must not use any digit grouping symbols.
declare @test varchar (50) = replace ('14,714.000 ', ',', ''); select @test; select cast (@test as float) select convert (float, @test) Can you add a short description? thanks, you know - code-only answers are considered low quality.
As mentioned above, the actual reason you get this error message, is that you are passing as a parameter to the CAST or CONVERT SQL Server functions, a value (varchar expression) that is invalid and cannot be converted to the desired data type.
You cannot cast the value in mysql using float type. You can use this simple trick 0 + column_name to convert it to float. Amazing! Nice trick. It's compact, and doesn't need to assume any specific amount of numbers past the decimal.
You can convert varchars to floats, and you can do it in the manner you have expressed. Your varchar must not be a numeric value. There must be something else in it. You can use IsNumeric to test it. See this:
declare @thing varchar(100)
select @thing = '122.332'
--This returns 1 since it is numeric.
select isnumeric(@thing)
--This converts just fine.
select convert(float,@thing)
select @thing = '122.332.'
--This returns 0 since it is not numeric.
select isnumeric(@thing)
--This convert throws.
select convert(float,@thing)
Use
Try_convert(float,[Value])
See https://raresql.com/2013/04/26/sql-server-how-to-convert-varchar-to-float/
9
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
