Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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

  • Difficulty Level : Expert
  • Last Updated : 28 Oct, 2021

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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!