CODEWITHSUNDEEP
sqloracle-databaseoracle11gtype-conversion
Csanchez
Csanchez

Reputation: 373

ORACLE SQL TO_NUMBER string value with comma

In an insert, I am passing a value like "9.9". How can I insert it into a numeric column like "NUMBER (4,2)"?

I try to_number ('9.9', '9G999D99') but in the table it is like '99' and I need it to be 9.9

enter image description here

enter image description here

Upvotes: 0

Views: 2406

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

If you pass a number, you'll insert a number. Otherwise, use TO_CHAR with appropriate format mask (and TO_NUMBER to explicitly inform Oracle what you're doing).

SQL> create table test (val number(4, 2));

Table created.

SQL> insert into test (val) values (1.1);

1 row created.

SQL> insert into test (val) values (to_number(to_char('2,2', '99D99')));

1 row created.

SQL> insert into test (val) values
  2    (to_number(to_char('3,3', '99D99', 'nls_numeric_characters=,.')));

1 row created.

SQL> select * From test order by val;

       VAL
----------
       1,1
       2,2
       3,3

Error appears if I use it in a wrong manner:

SQL> insert into test (val) values
  2    (to_number(to_char('4,4', '99D99', 'nls_numeric_characters=.,')));
  (to_number(to_char('4,4', '99D99', 'nls_numeric_characters=.,')))
                     *
ERROR at line 2:
ORA-01722: invalid number


SQL>

In Croatia, we have a decimal comma, not point; switch to decimal point:

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

Session altered.

SQL> select * From test order by val;

       VAL
----------
       1.1
       2.2
       3.3

SQL>

Upvotes: 2

Related Questions