Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more


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

Posted on 2011-09-09
Medium Priority
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

647 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