Solved

Oracle query  - Blank in field

Posted on 2014-11-12
5
548 Views
Last Modified: 2014-11-12
In my data set I have have a column that has 'Blanks' in it.  In other words there is nothing in the field.  What is the Oracle SQL query syntax to find only the records with the blank fields or the fields where there is nothing in the field.  thanks.
0
Comment
Question by:donnie91910
5 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 40438189
To find 'blanks':
where trim(column_name) is null

To find 'not blanks':
where trim(column_name) is not null

The trim may be optional depending on the data type and if the application allows spaces.

CHAR data types pad with spaces where VARCHAR2 does not.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 40438251
I wouldn't use TRIM, because that will cause fields with nothingbut space charactes to be treated as nulls

that is the intent in slightwv's post, but likely not what you're looking for.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 40438259
if you are looking for NULL values (nothing) as well as non-visible data (spaces, line feeds, carriage returns, etc) then you'll need more than just trim.

if this is the case then how exhaustive of a search would you want?

you can use regular expressions to look for common white-space but still not all non-visible characters.
0
 

Author Closing Comment

by:donnie91910
ID: 40438406
I used the 'Dump' function to find out what was really in the blank field and it is a hidden 'Null' value.   so I used slightwv's post and it seems to have worked.  Thanks.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40438697
if you are looking for NULL values (nothing) as well as non-visible data (spaces, line feeds, carriage returns, etc) then you'll need more than just trim.
AddOn: in these cases the LNNVL built-in function becomes quite handy (which itself is used in the WHERE clause as a true/false condition, that returns all rows matching this condition plus the "NULLs"...
Maybe you give it a shot, next time ;-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 102
Deny Oracle DBAs from Connecting  "/ as sysdba" 5 64
Best RAID for a BDD Oracle 4 69
capture vmstat info and insert it into an oracle table 31 20
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 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