0

I need to select from a table a column that is supposed to be float. However, some values come up as string. Is thee a way to replace the string with a constant (say 999) within the select statement?

Thanks

Aswathy S
  • 729
  • 1
  • 12
  • 41
Blue Moon
  • 4,421
  • 20
  • 52
  • 91
  • what is your purpose? why are you trying to replace it with 999? do you need to replace all the values for that column? – mcha Jul 11 '16 at 09:58
  • no I need to replace what is not numeric with 999 – Blue Moon Jul 11 '16 at 10:03
  • Ok, and are you sure replacing with 999 would not corrupt your results in a later stage? for example if you need to aggregate data and create a report, is the column somehow an ID or is it additive and you need to create aggregations and calculations on it – mcha Jul 11 '16 at 10:06
  • 1
    [edit] your question add some sample data and the expected output (_formatted_ text please, [no screenshots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557)) –  Jul 11 '16 at 10:06
  • 999 is just an example. I don't think the question is too complicated. What I need is to replace every non numeric value in a column within a select statement with a constant. If you have a column with values: 1,33,44,'hello',6,9,4,'whatever' I want to get: 1,33,44,999,6,9,4,999. so something like select if_not_numeric(column,999,column) from table – Blue Moon Jul 11 '16 at 10:10
  • The question is not too complicated but I am trying to advice you not to replace with a random value because it will most probably corrupt your results, check this duplicated question for your solution http://stackoverflow.com/questions/16195986/isnumeric-with-postgresql – mcha Jul 11 '16 at 10:12
  • It is an abstract question and the outcome won't be part of any production. Your over concern about good practice is rather off topic. Please limit your comments to the question and avoid confusion. – Blue Moon Jul 11 '16 at 10:14

1 Answers1

0

You may find useful answer here: https://stackoverflow.com/a/10307443/6359593 . I copied code here for lazy ones =)

create or replace function cast_to_int(text, integer) returns integer as $$
begin
    return cast($1 as integer);
exception
    when invalid_text_representation then
        return $2;
end;
$$ language plpgsql immutable;
Community
  • 1
  • 1
NLink
  • 485
  • 2
  • 5