CODEWITHSUNDEEP
oracle-database
HEEN
HEEN

Reputation: 4727

Update column values by removing values after decimal points and comma in oracle

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.

Upvotes: 0

Views: 225

Answers (1)

Chrᴉz remembers Monica
Chrᴉz remembers Monica

Reputation: 1904

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

Upvotes: 1

Related Questions