Link to home
Start Free TrialLog in
Avatar of mbroad02
mbroad02Flag for United States of America

asked on

How to SELECT null or blank columns in SQL

I am creating an extract in SQL of some Oracle database columns.  However, when some of the columns are blank, they do not SELECT.  I would like to know how to SELECT a given column and insure that it will SELECT it even if it is blank or null.
Thank you
Avatar of pollock_d

try something like...

SELECT NVL(column1,''),  NVL(column2,'')....etc

this will replace the NULL in the column with an empty string...\

it might work...havent tried it..
Avatar of mbroad02


i will try this and let you know--thanks
hi, can you post your query? It might be filtered out in your WHERE condition. Try to add this

WHERE <your conditions> OR (ColumnWithNull IS NULL)
Avatar of Naveen Kumar
if you have columns with null values and you want to select them
them you have to do as shown below :

select col1, col2
from table1
where col3 is null; -- this will get all records from table1 where col3 is null.

select col1, col2
from table1
where col3 is not null; -- this will get all records from table1 where col3 is not null.

if you want to treat a null value as a different value in the queries, then we have to use
something like as shown below :

select col1, col2
from table1
where nvl(col1,9999) >= 100 ; -- i assume col1 is of number datatype

select col1, col2
from table1
where nvl(col2,'Other Dept') in ('Marketing','Other Dept') ; -- i assume col2 is of varchar2 datatype

We can even use decode, nvl2, coalesce.. to handle null values.

Thank you for your many replies.  I am posting my query.  Notice that it is not a standard query--this one creates a fixed length text file.  
I am interested in pulling all columns in the query, whether null or not.  I do not know what columns may be null or not, so I need to check for that, and pull values if they are there, or space fill if it is null.
Please let me know how to accomplish this.

spool  c:\extract.txt
         ||     RPAD(SUBSTR(IDNT.SSN,1,9),9)
         ||     RPAD(SUBSTR('         ',1,9),9)
         ||     RPAD(SUBSTR(IDNT.LAST_NAME,1,30),30)
         ||     RPAD(SUBSTR(ADDR.ADDR_LINE1,1,30),30)
         ||     RPAD(SUBSTR(ADDR.ADDR_LINE2,1,30),30)
         ||     RPAD(SUBSTR(ADDR.CITY,1,20),20)
         ||     RPAD(SUBSTR(ADDR.ST_CD,1,2),2)
         ||     RPAD(SUBSTR(ADDR.ZIP_CD,1,9),9)
         ||     RPAD(SUBSTR('386004868',1,9),9)
         ||     RPAD(SUBSTR(' ',1,146),146)
         ||     RPAD(SUBSTR(CASE.DSCR,1,16),16)
         ||     RPAD(SUBSTR('C',1,1),1)
         ||     RPAD(SUBSTR('16  ',1,4),4)
         ||     RPAD(SUBSTR('48043',1,9),9)
         ||     RPAD(SUBSTR('0',1,1),1)
         ||     RPAD(SUBSTR('ARPTY.AMT_OUTSTANDING',1,10),10)
         ||     RPAD(SUBSTR('20080307',1,8),8)
         ||     RPAD(SUBSTR(' ',1,35),35)
  ("DKT"."CASE_ID"="CASE"."CASE_ID" (+)) AND
  ("DKT"."DT">=TO_DATE ('01-09-2007 00:00:00',
  "DKT"."DT"<TO_DATE ('02-03-2008 00:00:00',
  'DD-MM-YYYY HH24:MI:SS'));
	Spool off

Open in new window

Increased point value---really need a solution--thanks
Avatar of awking00
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I am testing and will let you know shortly--thank you!
This appears to have worked!! thanks very much.
Thank you!!
Glad to help :-)
dude, I suggested the NVL(column1,''),  NVL(column2,'')....etc .. ages ago, geez, you coulda had it working sooner...!