Re: to_number function

From: James David Smith <james(dot)david(dot)smith(at)gmail(dot)com>
To: "manno_it(at)libero(dot)it" <manno_it(at)libero(dot)it>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: to_number function
Date: 2012-11-14 23:23:09
Message-ID: CAMu32ACE_HOE+zA+tXcqTrp36X2j2+eEnDvcA3tSVgamYkO4jQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi Mauro,

I'm a bit confused about what you want to do.

If you have a field which has the value "3,54" in it then what number do
you want that to be? 354? 3.54? 3? 54?

If you want 3,54 to become 3.54 then might it not be easier to do a FIND
AND REPLACE in the Excel document to replace the comma with a decimal point
and then import it into Postgresql. The column could then already be set as
numeric rather than text.

I guess the other alternative is to do a kind of find and replace within
postgresql but I can't remember how to do that at the moment.

Thanks

James
On 14 Nov 2012 21:34, "manno_it(at)libero(dot)it" <manno_it(at)libero(dot)it> wrote:

> Hello people,
> it is my first post here! Thank you all for any suggestion and help.
>
>
> I'm a newbie in PostgreSQL.
>
> I'm working on a PostgreSQL table (*evaluations*) imported from an .xls
> one in which a column named "score" stores some values. To be able to
> import the table from .xls I had to set the TYPE of the PostgreSQL column
> "score" to TEXT, because the data stored are registered in different ways
> (previous values were generated by FileMakerPro...): NULL, or a number in
> different ways like: 3 or 3,5, or 3,50 or 3,75... to 10 (which is the
> maximum number accepted in this column.
>
> I need to convert the TYPE of my column from TEXT to NUMERIC (with the
> possibility to have decimal numbers with a maximum of 2 characters after
> the comma). I surfed the PostgreSQL Guide finding the to_number function,
> but it is really to difficult for me in this moment to understand the way
> to use it properly according to the variability of my data in the column
> "store".
> Anyone could help me, please?
> Bests,
> Mauro
>
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Igor Romanchenko 2012-11-15 13:52:18 Re: to_number function
Previous Message Mick 2012-11-14 22:27:38 Query on JSON field type