to_number function

[m***@libero.it]

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

[James David Smith]

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

Post by m***@libero.it
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

[Igor Romanchenko]

Post by m***@libero.it
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

Hi Mauro.
Can you give us some sample data for your problem (create table and some
inserts with sample data) ?