Re: Converting to number with given format

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(dot)klaver(at)aklaver(dot)com