Thread: to_number function
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,
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
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
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