Re: Converting to number with given format

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(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>