0

in relation with how to copy the records from output of oracle SQL developer to excel sheet

I'm using Oracle SQL developer. I've some queries which get some null values in the result grid. I'd cut/paste these values in Excel but null are pasted as empty, not as "null", so difficult to find them among cells that contains spaces. Is there any way to export null as "null" in Excel please?

Thank you.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sly
  • 26
  • 1
  • Probably it possible to find something in the import settings, but maybe the simplest option would be to use in your query something like CASE x WHEN NULL THEN '' ELSE x END – Jesusbrother May 13 '22 at 07:04

2 Answers2

1

If your column is of datatype varchar2 then:

select case  
            when your_column is null then 'null'
            else your_column  
       end your_column
from your_table;

If your column is of number or date datatype then just convert the non null values to string using to_char:

select case  
            when your_column is null then 'null'
            else to_char(your_column) 
       end your_column
from your_table;
Toni Antunović
  • 551
  • 3
  • 5
  • Hello, Thank you but all answers requiers to adapt sql queries. I was hoping that there would be a kind of setting in the oracle sql developer options that would allow to copy the null as "null" from any grid result, so that when pasting in excel, "null" would be written in cells. Something like that. – Sly May 16 '22 at 07:34
  • @Sly You can use the set null 'null' command and run the query in script (F5), it will show you 'null' or whatever you entered instead null values. You can export result as csv file, it will also respect the set null command. – Toni Antunović May 16 '22 at 08:24
  • Thank you for the tip. I've tried, but the result is just text, unstructured data, so it's not really usable for me (must rebuild all the structure, columns, time consuming). Or there is something I missed (?)... – Sly May 17 '22 at 09:39
0

You have multiple options:

  1. Using case with the IS NULL comparison
  2. Using NVL()
  3. Using DECODE() with something like DECODE(<col>, NULL, 'NULL', <col>)
Gnqz
  • 3,292
  • 3
  • 25
  • 35
  • Hello, Thank you but all answers requiers to adapt sql queries. I was hoping that there would be a kind of setting in the oracle sql developer options that would allow to copy the null as "null" from any grid result, so that when pasting in excel, "null" would be written in cells. Something like that. – Sly May 16 '22 at 07:34
  • Take a look at this https://stackoverflow.com/questions/31349673/oracle-sql-developer-displaying-null-in-column. Probably this is something closer to your needs in that case. – Gnqz May 16 '22 at 07:56
  • There is an sqlplus option set null "null". Did you try it? – Gnqz May 16 '22 at 08:28
  • Hi Gnqz, I'm sorry, I don't have "SQLplus", unfortunately, only Oracle SQL Developper. Nevertheless, I tried the set null 'null' command as suggested by Toni above, but result is just text, unstructured data, so needs to rebuild the structure (columns) each time which is time consuming. About the other link you gave: I tried to change some options on this advanced options, but still, when copying, it's not taken by clipboard as when pasting, nothing comes for these null values. – Sly May 17 '22 at 09:39