I have a column which stores values as 6,983.80, 12,287.73, 6,977.00

so I want to update the above values with 6984, 12288 and 6977 respectively.

Please help.

CodePudding user response：

You should be able to use the ROUND function for that.

select ROUND(CAST(REPLACE('6983.80', ',') as number)), ROUND(1.4), ROUND(1.5), ROUND(1.6) from dual;

yields 1,2,2.

In your case, as your input is NVARCHAR2, it would be

update table1 set col1 = CAST(ROUND(CAST(REPLACE(col1, ',') as NUMBER)) as NVARCHAR2(6));

Please save numbers as a number type and not as string to avoid complicated castings

Documentation is here