Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mssql convert varchar to float

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?

like image 366
Smudger Avatar asked Jul 18 '13 17:07

Smudger


People also ask

Why is my varchar not converting to float in SQL Server?

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.

How to convert @test varchar 50 to float?

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.

Why do I get an error when converting A varchar expression?

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.

How to cast a value to float in MySQL?

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.


2 Answers

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)
like image 185
Bill Gregg Avatar answered Sep 18 '22 14:09

Bill Gregg


Use

Try_convert(float,[Value])

See https://raresql.com/2013/04/26/sql-server-how-to-convert-varchar-to-float/

like image 9
Mthobisi Dube Avatar answered Jan 01 '70 00:01

Mthobisi Dube