CODEWITHSUNDEEP
sqloracle-database
124697
124697

Reputation: 21893

How can I replace all zeroes returned with blank space

How can I remove all zeros returnd from an sql query

edit: My table has a lot of cells that contain 0. i want all zeros to be replaced by a blank space

for example:

location |count

location1 | 0

localtion2| 2

turned into

location |count

location1 |

localtion2| 2

Upvotes: 2

Views: 16287

Answers (4)

kevin cline
kevin cline

Reputation: 2736

SELECT DECODE(n, 0, ' ', n) ...

Upvotes: 1

tbone
tbone

Reputation: 15473

I assume you mean leading zeros? Try ltrim:

select ltrim('00001234', '0') from dual;

Upvotes: 2

Mark Mooibroek
Mark Mooibroek

Reputation: 7696

Use the case statement maybe ?

SELECT 
    CASE 
        WHEN numbers = 0 THEN "Zero" 
        WHEN numbers = -1 THEN "Under zero"
        ELSE "Not Empty" 
    END
FROM t_able

Upvotes: 1

Jacob Ewald
Jacob Ewald

Reputation: 2178

The function is oddly enough named REPLACE. Check Oracle's documentation here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions134.htm

SELECT REPLACE(COLUMN_NAME,'0',' ') "Column Name" FROM YOUR_TABLE_NAME;

Upvotes: 6

Related Questions