Solved

Oracle query  - Blank in field

Posted on 2014-11-12
5
530 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
Extract the first word (before the , ) 2 37
Oracle Next Available Number 2 30
oracle 11g 23 47
Oracle - SQL Where clause causing Invalid Number Error 4 26
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now