How to Ignore 0(zero) Values in SQL Query?

In SQL, many times we are required to display all the rows of a table in which the entries having 0(zero) as an entry/value are ignored. This is achieved through REPLACE command in SQL. In this article, we will discuss how to ignore 0(zero) and replace them with an empty space in SQL. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table MARKS inside the database GeeksForGeeks. This table has 4 columns namely ID, ENG, MATHS, and PHYS containing student IDs and marks of various subjects.

Query:

CREATE TABLE MARKS(
ID INT,
ENG INT,
MATHS INT,
PHYS INT);

Output:

Step 4: Insert 5 rows into the MARKS table and enter some 0(zero) values as subject marks to demonstrate how to ignore such entries.

Query:

INSERT INTO MARKS VALUES(1,99,0,89);
INSERT INTO MARKS VALUES(2,0,65,78);
INSERT INTO MARKS VALUES(3,83,85,0);
INSERT INTO MARKS VALUES(4,0,77,0);
INSERT INTO MARKS VALUES(5,97,45,54);

Output:

Step 5: Display all the rows of the MARKS table including the 0(zero) values.

Query:

SELECT * FROM MARKS;

Output:

Step 6: Display all the rows of the MARKS table ignoring the 0(zero) values. This is achieved through REPLACE command which replaces all 0’s with an empty blank. 

Syntax:

REPLACE(Column_name, value_to_be_replaced, new_value)

To perform this for more than one column, place a comma and then write multiple replace commands.

Query:

SELECT ID, REPLACE(ENG,0,'') "ENG", REPLACE(MATHS,0,'') "MATHS", REPLACE(PHYS,0,'') "PHYS" FROM MARKS;

Output: