mssql convert varchar to float

Question

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?

`select CONVERT(float, '0.123')` works. Are u sure ther're no extra characters in that variable? — Yuriy Galanter, Jul 18 '13 at 17:50

score 33 · Accepted Answer · answered Jul 18 '13 at 17:53

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)

Just be aware that `ISNUMERIC` can have some [funny behavior](http://stackoverflow.com/questions/4522056/how-to-determine-the-field-value-which-can-not-convert-to-decimal-float-int-i/4522185#4522185). — Stainy, Jul 18 '13 at 17:58

score 11 · Answer 2 · edited Oct 31 '17 at 10:10

11

Use

Try_convert(float,[Value])

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

edited Oct 31 '17 at 10:10

rene

41,474
78
114
152

answered Oct 31 '17 at 10:09

Mthobisi Dube

121
1
4

score -1 · Answer 3 · edited Jul 17 '15 at 07:27

DECLARE @INPUT VARCHAR(5) = '0.12',@INPUT_1 VARCHAR(5)='0.12x';
select CONVERT(float, @INPUT) YOUR_QUERY ,
case when isnumeric(@INPUT_1)=1 THEN CONVERT(float, @INPUT_1) ELSE 0 END AS YOUR_QUERY_ANSWERED

above will return values

however below query wont work

DECLARE @INPUT VARCHAR(5) = '0.12',@INPUT_1 VARCHAR(5)='0.12x';
select CONVERT(float, @INPUT) YOUR_QUERY ,
case when isnumeric(@INPUT_1)=1 THEN CONVERT(float, @INPUT_1) ELSE **@INPUT_1** END AS YOUR_QUERY_ANSWERED

as @INPUT_1 actually has varchar in it.

So your output column must have a varchar in it.

mssql convert varchar to float

3 Answers3

Linked

Related