1

I want to replace '' or '0.00' values from specific column with '' only, without adding new column. I was trying to do as below but its not working.

DB columns:

  id    rt1     rt2
   x    0.345   
   y    0.00    0.345

Expected result:

 id rt1     rt2   new column
  x 0.345            0.345
  y 0.00    0.345    0.345

Based on conversation ,below is my expected result.

 id      rt1    rt2      new column
 'a'    0.345,  null     0.345
 'b'    0.00,  0.345     0.345
  'c'   0.345, 0.445     more 0.00 values cant be in both columns
  'd'   0.445, 0.345     more 0.00 values cant be in both columns
 'e'    0.00,  0.00      null
 'f'    0.00,  null      null
 'g'    null,  0.00      null

My condition: If rt1 is 0.00 or '', then '' else rt1. Same for r2 and then create new column.

SELECT id,
   CASE WHEN rt1 = '0.00' THEN '' ELSE rt1 END AS rt1,
   CASE WHEN rt2 = '0.00' THEN '' ELSE rt2 END AS rt2,
   Case when rt1 != ''  then rt1 else rt2 end as new_rt_column
   
FROM tablename;
John83
  • 97
  • 1
  • 8
  • 2
    If the value is already `''` why do you want to replace it with `''`? – forpas Jan 06 '22 at 17:54
  • (a) You're missing commas after the 4th and 5th column. (b) Use `CASE` expressions for the 4th and 5th column like you did for the 6th column. (c) As @forpas already pointed out, there's little point in replacing the empty string with the empty string. But it's not *technically* wrong. – sticky bit Jan 06 '22 at 18:00
  • Because in the same column sometimes its ' ' and sometimes ' 0.00'. So I want to assign ' ' for both conditions. May be you can ignore ' ' condition but replace '0.00' with ' '. – John83 Jan 06 '22 at 18:02
  • Do you want a select query or to update the table? – forpas Jan 06 '22 at 18:05
  • I want select query and not update. – John83 Jan 06 '22 at 18:06
  • @stickybit I have edited the missing commas, If I use case statement, its adding new column for replacement which I dont want. – John83 Jan 06 '22 at 18:09
  • If you do not want a new column then why did you create the column `new_rt_column'. the `case` expression does not create a new column, the column was the result of you putting an expression in the query, it would have been created no matter what you put in place of case (providing what you put is still valid). Please post sample data and the desired results of that data, all as text - **no images**. Your data should include all conditions your question mentions: '', 0.00, something else. Side Note: There is no `IF` in SQL the only way to select alternatives is CASE. – Belayer Jan 06 '22 at 18:30
  • @Belayer added more details and expected result. Please check now – John83 Jan 06 '22 at 18:58

1 Answers1

0

You can use the function GREATEST() to get the greatest of the 2 values and NULLIF() to return null if the greatest value is 0.00:

SELECT id, 
       rt1, 
       rt2,
       NULLIF(GREATEST(NULLIF(rt1, '')::float, NULLIF(rt2, '')::float), 0) AS new_rt_column
FROM tablename;

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • After new derived column (rt1, rt2), if I write this statement, my new column is containing 0.00. SELECT id, CASE WHEN rt1 = '0.00' THEN '' ELSE rt1 END AS rt1, CASE WHEN rt2 = '0.00' THEN '' ELSE rt2 END AS rt2, Case when rt1 != '' then rt1 else rt2 end as new_rt_column FROM tablename; – John83 Jan 06 '22 at 18:19
  • @John83 are you sure that the existing values are exactly `'0.00'` or do they contain leading or trailing spaces? – forpas Jan 06 '22 at 18:21
  • Please add my third statement in your code and check what values you see. – John83 Jan 06 '22 at 18:21
  • @John83 I can't see any values because I don't have your data. Post sample data in a fiddle: https://dbfiddle.uk/?rdbms=postgres_12 and expected results to clarify what you want. – forpas Jan 06 '22 at 18:23
  • I have edited the post with additional details. Please check now. – John83 Jan 06 '22 at 18:57
  • @John83 what is the data type of the columns rt1 and rt2? – forpas Jan 06 '22 at 19:10
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/240802/discussion-between-john83-and-forpas). – John83 Jan 06 '22 at 19:13