?
Solved

SQL - replace zero values in a table with blank or null

Posted on 2006-04-06
5
Medium Priority
?
7,562 Views
Last Modified: 2012-05-05
Is there an easy way to change all the zero values
in a SQL table to blank or null and leave the positive or
negative values.

eg table_1 before

Field1      Field2
0      1
12      100
14      0
17      18
0      0
3      4      

eg table_1 after

Field1      Field2
      1
12      100
14      0
17      18

3      4

I am using Oracle SQL version 8.

Many Thanks,

Jason.
0
Comment
Question by:JasonAsh
5 Comments
 
LVL 6

Accepted Solution

by:
Taconvino earned 500 total points
ID: 16394520
Update table_1 set Field1 = null where Field1 = 0

Update table_1 set Field2 = null where Field2 = 0

That should work...

+TCV
0
 
LVL 3

Expert Comment

by:_Gerry_
ID: 16396788
or do it in one update statement !

update table_1
set field1 = case field1 when 0 then null else field1 end,
     field2 = case field2 when 0 then null else field2 end
where field1=0 or field2=0
0
 
LVL 3

Expert Comment

by:_Gerry_
ID: 16396797
oops, that was SqlServer T/SQL syntax - it might work on Oracle :-) If not similar struture/approach might work.
0
 
LVL 11

Expert Comment

by:WelkinMaze
ID: 16398566
Hi there,

You can try one of these 3 ways:

With two separate queries:

UPDATE table_1 SET field1 = NULL WHERE field1 = 0;
UPDATE table_1 SET field2 = NULL WHERE field2 = 0;

or

With one shorter query if zero values prevail:

UPDATE table_1
SET field1 = DECODE (field1, 0, NULL, field1),
       field2 = DECODE (field2, 0, NULL, field2);

or

With one longer query if zero values are not too much:

UPDATE table_1
SET field1 = DECODE (field1, 0, NULL, field1),
       field2 = DECODE (field2, 0, NULL, field2)
WHERE field1 = 0 or field2 = 0;
0
 

Author Comment

by:JasonAsh
ID: 16398634
Thanks all,

The code all worked but because Taconvino  was first out of the blocks heer gets the points.

Thanks J
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This video teaches viewers about errors in exception handling.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question