On 9/19/18 6:33 AM, Gabriel Furstenheim Milerud wrote:
Sorry,
So basically what I'm trying to achieve is the following. There is an
input file from the user and a configuration describing what is being
inserted. For example, he might have Last activity which is 'YYYY-MM-DD
HH:mi:ss' and Join date which is only 'YYYY-MM-DD' because there is no
associated timing. For dates this works perfectly and it is possible to
configure what the input from the user will be. Think it is as a
dropdown where the user says, this is the kind of data that I have.
Maybe that is not possible with numbers? To say in a format something
like "my numbers have comma as decimal separator and no thousands
separators" or "my numbers are point separated and have comma as
thousands separator"
Yeah that is a problem because it depends on the locale information in
the database you are entering the data:
show lc_numeric;
lc_numeric
------------
en_US
select to_number('10.000,00', '99999D00');
to_number
-----------
10.00
select to_number('10.000,00', '99999.00');
to_number
-----------
10.00
set lc_numeric = 'de_DE';
SET
select to_number('10.000,00', '99999D00');
to_number
-----------
10000.0
select to_number('10.000,00', '99999.00');
to_number
-----------
10.00
D and G(group separator) work using the locale information set in the
database. I know of no way to pass the information in with the format
string. Off the top of my head I would say that this conversion would
need to be done at point of input. Have user select their decimal and
group separators and then convert to a universal format.
Nice thing of having a string for the format is that I can use it as a
parameter for a prepared statement.
Thanks
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
