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.klaver@aklaver.com