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

I'm not completely sure that that actually works

SELECT to_number('9,134', '9V3') = 9

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

On Wed, 19 Sep 2018 at 14:57, Adrian Klaver <adrian.klaver@aklaver.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.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

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

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

On Wed, Sep 19, 2018 at 6:34 AM Gabriel Furstenheim Milerud <
furstenheim@gmail.com> wrote:

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"

Would stripping out the thousand separator, and leaving in the decimal
separator work?

SELECT replace('9,000.34',',','')::numeric;
replace
---------
9000.34

If so, then (conceptually) does this work?

SELECT replace(

replace(my_numeric_string, user_thousand_sep, ''),

user_decimal_sep, system_decimal_sep

)::numeric

Or maybe I'm missing something about this!

Cheers,

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Hi Ken,
Thanks a lot, that's a cool idea and I think that it will cover my needs.

On Thu, 20 Sep 2018 at 02:04, Ken Tanzer <ken.tanzer@gmail.com> wrote: