Thread: Converting to number with given format

On 9/19/18 5:38 AM, Gabriel Furstenheim Milerud wrote:
> Hello,
> I'd like to convert a string number to a number being able to provide 
> the custom format.
> With dates it works perfectly fine, so that I can do:
> 
> SELECT to_date('18 09 10', 'YY MM DD')
> 
> Is there something similar with numbers?
> 
> SELECT to_number('9,000',some_format) =9;

  SELECT to_number('9,000', '9V3')::int;
  to_number
-----------
          9

> SELECT to_number('9,000',another_format) =9000;

SELECT to_number('9,000', '99999');
  to_number
-----------
       9000

> 
> It is not clear to me what some_format should be and what another_format 
> should be so that those selects are equal.
> 
> I've read the documentation but I can't find a similar example. In 
> stackoverflow they don't provide a solution either: 
> https://stackoverflow.com/questions/18882942/postgresql-convert-a-string-with-commas-into-an-integer
> 
> Thanks
> Gabriel Fürstenheim


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/19/18 6:11 AM, Gabriel Furstenheim Milerud wrote:
> I'm not completely sure that that actually works
> 
> SELECT to_number('9,134','9V3') =9

SELECT (to_number('9,134', '99999')/1000)::numeric(4,3);
  numeric
---------
    9.134

> 
> It's true when it should be false (it should be 9.134). Also it is 
> completely dependent on the number of digits. So for example:
> 
> SELECT to_number('19,134','9V3')
> 
> Is 1, not 19.134 or even 19

We probably ought to back this question up and ask what is you want to 
achieve in general?


> 
> On Wed, 19 Sep 2018 at 14:57, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 9/19/18 5:38 AM, Gabriel Furstenheim Milerud wrote:
>      > Hello,
>      > I'd like to convert a string number to a number being able to
>     provide
>      > the custom format.
>      > With dates it works perfectly fine, so that I can do:
>      >
>      > SELECT to_date('18 09 10', 'YY MM DD')
>      >
>      > Is there something similar with numbers?
>      >
>      > SELECT to_number('9,000',some_format) =9;
> 
>        SELECT to_number('9,000', '9V3')::int;
>        to_number
>     -----------
>                9
> 
>      > SELECT to_number('9,000',another_format) =9000;
> 
>     SELECT to_number('9,000', '99999');
>        to_number
>     -----------
>             9000
> 
>      >
>      > It is not clear to me what some_format should be and what
>     another_format
>      > should be so that those selects are equal.
>      >
>      > I've read the documentation but I can't find a similar example. In
>      > stackoverflow they don't provide a solution either:
>      >
>     https://stackoverflow.com/questions/18882942/postgresql-convert-a-string-with-commas-into-an-integer
>      >
>      > Thanks
>      > Gabriel Fürstenheim
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

Gabriel Furstenheim Milerud <furstenheim@gmail.com> writes:

> 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"
>
> Nice thing of having a string for the format is that I can use it as a
> parameter for a prepared statement.
>

I think this is normally something much better dealt with at the client
level. Things like comma separator/grouping in numbers is really just a
'human' thing and is very locale dependent. The values 9,999 and 9999
are the same values. Things can quickly become complicated as you can
have locale information at both the server and client end and they may
not be the same. 

As you should always be sanitising your data before inserting into the
database anyway, you may as well just add this as another check at the
client end.

Tim

-- 
Tim Cross