Sign Up Login


  1. Categories >> Software >> Databases >> SQL PLSQL
  2. Suggest New Category

how to eliminate null values in a column i.e
table vlaues
1 2 3
NULL 3 4
1 5 NULL
i want output like this
1 2 3
3 4
1 5
i dnt want to use nvl is null and i dnt want replace the
NULL value with any value i.e nvl(col,o);

Question Posted / ramkrish.143

Answers were Sorted based on User's Feedback



how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / chandra shekhar

Dear Lavanya,
You cannot equate the NULL with the operators like = and !=

NULLs can be equated with "IS NULL" or "IS NOT NULL"
(Double quotes " not included)

In u r query if u say IS NOT NULL, then also as per question asked in the very beginning it will not suffice.

As there are multiple columns with multiple NULLs in all the given columns .

Regards
J

Is This Answer Correct ?    3 Yes 1 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / dipti

The only option I feel is

SELECT * FROM TABLE
WHERE COL_NAME IS NOT NULL

will return the non null values which I think is asked for
this question.

Is This Answer Correct ?    2 Yes 0 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / hiya

SELECT REPLACE(A,'NULL',' '),
REPLACE(B,'NULL',' '),
REPLACE(C,'NULL',' ')FROM TEST1

Is This Answer Correct ?    0 Yes 0 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / srinu

Hi Ramkrish,

select *from k;

SNO
-----
1
2
3


3
4



9 rows selected

select sno from k where sno is not null;

SNO
-----
1
2
3
3
4

Is This Answer Correct ?    0 Yes 0 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / pankaj

suppose u have table named as emp

emp_id dept_id last_name salary

1 10 N1 10000

2 20 N2 10000

3 N3 12000

4 N4 12000

5 30 N5 15000


NOW IF U WANT TO RETRIEVE THE ROWS WITH ELIMINATING NULL
VALUES.

EX-

SELECT EMP_ID,DEPT_ID,LAST_NAME,SALARY
FROM EMP
WHERE DEPT_ID IS NOT NULL;

SO IN THE OUTPUT NULL VALUES WILL NOT BE RETRIVED MEANS
RECORD FOR EMP_ID 3 AND 4 WILL NOT BE RETRIVED.

REGARDS R

Is This Answer Correct ?    0 Yes 0 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / venkat reddy

1 select e.*,
2 case
3 when
4 comm is null then '0'
5 when
6 comm is not null then
7 to_char(comm) end as comm
8* from emp e
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO COMM
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------
7839 KING PRESIDENT 17-NOV-81 5000 10 0
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 0
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 0
7566 JONES MANAGER 7839 02-APR-81 2975 20 0
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 0
7902 FORD ANALYST 7566 03-DEC-81 3000 20 0
7369 SMITH CLERK 7902 17-DEC-80 800 20 0
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 300
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 500
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 1400
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 0
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 0
7900 JAMES CLERK 7698 03-DEC-81 950 30 0
7934 MILLER CLERK 7782 23-JAN-82 1300 10 0

14 rows selected.

Is This Answer Correct ?    0 Yes 0 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / vijay_1994

please use group function is ignore the null values

Is This Answer Correct ?    0 Yes 0 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / shekharjchandra

Generally when u give query like
Select * from tablename; you will not get NULL displayed in Oracle SQL* Plus. I am not sure what tool you are using.

In SQL server you will get the output displayed NULL for null values.

If you still getting NULL as display value for null values then probably u have to do small change.

at SQL prompt use SQL* Plus command

SQL> SET NULL ""


now u give u r query, u will not get NULL displayed for null values

Regards
J

Is This Answer Correct ?    0 Yes 1 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / jayendra

Hi
You Can Replace the null without using nvl
function.
using replace key world
like this
Select Replace(values,'null')
from demo where values!=NULL;
it will display the result like this
1 2 3
3 4
1 5

Thanks
J.N.Tripathi
9868703898

Is This Answer Correct ?    0 Yes 1 No

how to eliminate null values in a column i.e table vlaues 1 2 3 NULL 3 4 1 5 NULL ..

Answer / j.n.tripathi

Hi
Sorry To ALL.....
For Above Answer
Writing Mistake there.....

You Can Replace the null without using nvl
function.
using replace key world
like this
Select Replace(values,'null')
from demo
it will display the result like this
1 2 3
3 4
1 5

Thanks
J.N.Tripathi
9868703898

Is This Answer Correct ?    0 Yes 3 No

Post New Answer

More SQL PLSQL Interview Questions

how to show all tables with 'mysql'? : Sql dba

0 Answers  

Table Student has 3 columns,Student_id,Student_Name & Course_Id. Table Course has 2 columns, Course_Id & Course_Name.Write a query to listdown all the Courses and number of student in each course.

10 Answers   TCS,

what is a constraint? Tell me about its various levels. : Sql dba

0 Answers  

What packages are available to pl/sql developers?

0 Answers  

Can we join tables without foreign key?

0 Answers  




What is a table in a database?

0 Answers  

How to display the current date in sql?

0 Answers  

How can I see all tables in sql?

0 Answers  

In what condition is it good to disable a trigger?

0 Answers  

What is the main difference between sql and pl/sql?

0 Answers  

How to display all Friday's in a year with date?

4 Answers   Flipkart,

what is “go” in t-sql? : Transact sql

0 Answers  

For more SQL PLSQL Interview Questions Click Here
Categories