Re: Converting to number with given format

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  > 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 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com