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

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?

CodePudding user response：

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.

CodePudding user response：

Two options.

Set up table:

create table string_conv(id integer, num_column varchar);
insert into string_conv values (1, 83.420), (2, 43.317), (3, 149.317 );
select * from string_conv ;
 id | num_column 
---- ------------
  1 | 83.420
  2 | 43.317
  3 | 149.317

First option leave as string field:

update string_conv set num_column = translate(num_column, '.', '');
 select * from string_conv ;
 id | num_column 
---- ------------
  1 | 83420
  2 | 43317
  3 | 149317

The above changes the value format in place. I means though that if new data comes in with the old format, 'XX.XXX', then those values would have to be converted.

Second option convert to integer column:

truncate string_conv ;
insert into string_conv values (1, 83.420), (2, 43.317), (3, 149.317 );
alter table string_conv alter COLUMN num_column type integer using translate(num_column, '.', '')::int;
select * from string_conv ;
 id | num_column 
---- ------------
  1 |      83420
  2 |      43317
  3 |     149317
\d string_conv 
              Table "public.string_conv"
   Column   |  Type   | Collation | Nullable | Default 
------------ --------- ----------- ---------- ---------
 id         | integer |           |          | 
 num_column | integer |           |          |

This option changes the format of the values and changes the type of the column they are being stored in. The issue is here is that from then on new values would need to be compatible with the new type. This would mean changing the input data from 'XX.XXX' to 'XXXXX'.