Search Postgresql Archives

Re: Converting to number with given format

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

Thanks



On Wed, 19 Sep 2018 at 15:22, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux