CODEWITHSUNDEEP
sqloracle-database
kirthi kumar
kirthi kumar

Reputation: 9

Decimal point is removed before update to character variable

I have data in the below format and I want to update a destination table column of type varchar2 with below values. But the problem is it updates as .462 instead of 0.462 by using trim with leading '0'.

source              destination column
-----------------   ------------------
0000004.304300000   4.3043
0000005.504500000   5.5045
0000141.400000000   141.4
0000138.900000000   138.9
0000000.462000000   0.462
0000000.000297000   0.000297

Upvotes: 0

Views: 39

Answers (1)

Littlefoot
Littlefoot

Reputation: 142993

A little bit of TO_CHARing and TO_NUMBERing with appropriate format mask might do the job. Have a look at the example:

SQL> create table test (source varchar2 (20), destination varchar2(20));

Table created.

SQL> insert into test (source)
  2    select '0000004.304300000' from dual union all
  3    select '0000000.462000000' from dual union all
  4    select '0000141.400000000' from dual union all
  5    select '0000033.000000000' from dual;

4 rows created.

SQL> alter session set nls_numeric_characters = '.,';

Session altered.

SQL> update test set
  2    destination = rtrim(to_char(to_number(source), 'fm999990D99999999'), '.');

4 rows updated.

SQL> select * From test;

SOURCE               DESTINATION
-------------------- --------------------
0000004.304300000    4.3043
0000000.462000000    0.462
0000141.400000000    141.4
0000033.000000000    33

SQL>

Upvotes: 1

Related Questions