search the column with decimal value for '.'

Question

I have a table with a1 and a2 float columns,

The values in a2 are calculated from a1, as a2 = 3*a1

The condition is:

If the value in a1 is 9.5, I need to get the ceiling value in a2 i.e., if the numeric value after the decimal point is greater than or equal to 5 I need to get ceiling value, else I need to get the floor value.

I have written below query

SET    a2 =(case when substring(cast((a1 * 3) as varchar(6)),CHARINDEX('.',(a1*3)),1) >=5 then CEILING(a1 * 3) else FLOOR(a1 * 3) end) from table

but it obviously returns the below error:

Conversion failed when converting the varchar value '.' to data type int.

Since it, can't take varchar into ceiling or floor.

Is there any way by which I can achieve this?

Your help will be greatly appreciated.

The value of a2 keeps changing based on a1, if a1 is 4.5 a2 should ceiling of that, if a1 is 4.9 a2 should be again ceiling value but if a1 is anything below 4.5 as 4.3,4.2,4.1 then it should be a floor value

Any other approach for this would also do except ceiling and floor.

Answer 1

That because you trying to compare varchar >=5, cast it to INT:

SET a2 =(case when cast(substring(cast((a1 * 3) as varchar(6)),CHARINDEX('.',(cast((a1 * 3) as varchar(6))),1) as INT) >=5 then CEILING(a1 * 3) else FLOOR(a1 * 3) end) from table

Answer 2

How about using round()? It implements this logic directly in the database:

set a2 = round(a1, 0);

An alternative method is to subtract 0.5:

set a2 = floor(a1 + 0.5)

If you want a2 as a string value (you say you want a float but the code returns a string), then use str():

set a2 = str(a1)

str() rounds by default.