0

So I have the following situation: 

create table test1(c1 number, c2 varchar2(250 char));
/
insert into test1 values(0.11, 0.11);
/
select * from test1;

will give me:

C1       C2
0.11    .11

I want to store a number like 0.11 as it is, without removing the 0. Problem is that in that varchar2 column I can also store values like "1.123", "0.123456789", "test123". So basically it can be a number with any precision or even a string.
I think my only chance is to use the "fmt" parameter of to_char, but I can't find a mask that could solve any given number of digits and strings

Luca Iulian
  • 1

2 Answers2

0

You can try below using to_char() and fmt as '90D90'

create table test1(c1 number, c2 varchar2(250 char))END;

/

insert into test1 values(0.11, to_char(0.11, '90D90'))END;

/

select * from test1;
Fahmi
  • 37,315
  • 5
  • 22
  • 31
  • Please do not use `;` **and** `/` to separate statements (`/` will run the `create table` twice!, see [here](https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql/10207695#10207695) for details) –  Nov 08 '18 at 09:55
  • I need a solution that will work with any general situation. "SELECT to_char(0.11111, '90D90') FROM DUAL" will not work as expected for example. Even worse the value could be a string also: "SELECT to_char('test123', '90D90') FROM DUAL" will throw an error – Luca Iulian Nov 08 '18 at 09:56
  • @LucaIulian, what do u mean by general situation - could you please explain a bit more – Fahmi Nov 08 '18 at 09:57
0

Found a solution using regex:

select regexp_replace('.11115', '^(\.\d+)$', '0\1') from dual

This searches if it starts with a dot and continues only with digits. Won't crash if you pass it a string and the precision length doesn't matter

Luca Iulian
  • 1