Search code examples
postgresqltranslate

How to apply Translate function on all rows within a column in postgresql

In a dataset I have, there is a columns contains numbers like 83.420, 43.317, 149.317, ... and this columns is stored as string. The dot in the numbers doesn't represent decimal point, i.e., the number 83.420 is basically 83420 etc. One way to remove this dot from numbers in this column is to use TRANSLATE function as follows:

SELECT translate('83.420', '.', '')

which returns 83420. But how I can apply this function on all the rows in the dataset? I tried this, however, I failed:

SELECT translate(SELECT num_column FROM my_table, '.', '')

I face with error SQL Error [42601]: ERROR: syntax error at end of input. Any idea how I can apply translate function on one column in data entirely? or any better idea to use rather than translate?

Solution

  • You can even cast the result to numeric like this:

    SELECT translate(num_column, '.', '')::integer from the_table;
-- average:
SELECT avg(translate(num_column, '.', '')::integer from the_table;

    or use replace

    SELECT replace(num_column, '.', '')::integer from the_table;
-- average:
SELECT avg(replace(num_column, '.', '')::integer) from the_table;

    Please note that storing numbers as formatted text is a (very) bad idea. Use a native numeric type instead.