Need to modify the output by replacing values in output records result of execution of SQL

Posted on 2011-09-09
Last Modified: 2012-06-22
I have an SQL Query written.

It gives 70,000+ records. The problem is I do not know how to figure out to replace the value of one of the resulting columns whose values are blank and null with my custom value


output displayed is

Emp Id    Emp Name
---------    -------------

1                              << Null Value

2                 xyzabc

3                 abcxyz

4                            << Just a space and Not Null

5                poiuytr

In the above records 1 and 4 have no value( null and space ) associated and in the above ouput I want those employee names to be displayed as 'abcdef', so the output would be like

Emp Id    Emp Name
---------    -------------

1                abcdef

2                 xyzabc

3                 abcxyz

4                abcdef

5                poiuytr

I have used nvl method in my sql but still the null value is not replaced

Please calling for experts who can help me in successfully replacing null and blank space value with the value I would like to place 'abcdef' in the example
Question by:XxtremePro
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2

Expert Comment

ID: 36513779
something like this

select empid, decode(empname, nvl(empname, ''), 'abcdef'
                              '',               'abcdef'
                                , empname) empnamenonullsorblanks

sorry i can not testing it right now but is the idea.

with nvl you replace the null value in this case with a blank.
then with decode you are doing a kind of "if" so this means

if empname  is blank then output 'abcdef', else output empname value

LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 36513780
Did you try trim like I suggested in the last question?  I so, did it not work?

Author Comment

ID: 36513803

** Error in the question I asked above**

In the above question posted there is a mistake in the line

I have used nvl method in my sql but still the 'space'  value needs to be replaced
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 36513813
trim.... Let me see .. I did not quite pay attention to that part in the previous question.. Let me try and post the answer

Author Comment

ID: 36513870
sdstuber.. whoever you are...

Your solutions work amazing... I am in praise of your glory..... wonderful...smack it got it..:)

Author Closing Comment

ID: 36513880
This person sdstuber has got the knowledge which answered and matched my requirement

Kudos to him!!!

Author Comment

ID: 36513888

I had to traverse through the 70K records to get to see whether there are any null/empty values for emp_name

Can you suggest some easy way to test whether it has any such values and to make sure... Please let me know

Thanks in advance
LVL 74

Expert Comment

ID: 36514018
to find rows missing a name completely

select * from your_table
where emp_name is null

to find rows with a blank for a name

select * from your_table
where trim(emp_name) is null
and length(emp_name) > 0

Author Comment

ID: 36514034
I was talking about finding rows in the result of the sql where some are null and some are blank


Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

717 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