Solved

How do I query for blank spaces when I don't know how many blank spaces could exist

Posted on 2011-09-23
14
370 Views
Last Modified: 2012-05-12
I am trying to find out how many records hae a blank value in a field. I tried the following query because I don't know how many blank spaces could exist

Select count(*) from bib_holdings where publication_date like ' % ';

Is there a more accurate query that would pick all the records that would have any number of blank spaces in the publication_date field?
0
Comment
Question by:sikyala
[X]
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
14 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 36588418
where publication_date like '% %';
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 36588421
where regexp_like(publication_date,' ')
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 36588423
where replace(publication_date,' ') != publication_date
or (replace(publication_date,' ')  is null and publication_date is not null)
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 74

Expert Comment

by:sdstuber
ID: 36588435
your original where clause would only count rows that begin and end with a space

the three above will count rows that have at least one space anywhere in the string
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 36588583
My personal choice would be:

WHERE INSTR(PUBLICATION_DATE, ' ') > 0
0
 
LVL 15

Assisted Solution

by:x77
x77 earned 100 total points
ID: 36589556
To find records with 3+ Spaces:

select *  
from YourTable
where (length(YourFld)-length(replace(YourFld,' ','')))>2;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36589567
http:#36589556  doesn't handle nulls properly

for instance,  if YourFld= '         ',  it will fail


see  http:#36588423
0
 

Author Comment

by:sikyala
ID: 36589651
yes i just realized that because the field has a date datatype it won't allow blank spaces so my queries are accurate when they return no rows
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36589667
>> field has a date datatype

if publication_date is really a DATE  then this condition doesn't make any sense

   publication_date like ' % ';

It's legal syntax because you'll get an implicit conversion from date to string but it's not helpful
it simply adds extra work to your query for no functional gain at all
0
 

Author Comment

by:sikyala
ID: 36589681
i didn't realize that it wasn't possible until after I asked the question and tried different tests including updating a record with a blank space in the publication_date field
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36589727
not only is the condition not helpful,  it can even be misleading,
again, because of implicit conversions


SQL> create table bib_holdings(publication_date date);

Table created.

SQL> insert into bib_holdings values (sysdate);

1 row created.

SQL> insert into bib_holdings values (sysdate-1);

1 row created.

SQL> insert into bib_holdings values (sysdate+1);

1 row created.

SQL> commit;

Commit complete.

SQL> Select count(*) from bib_holdings where publication_date like ' % ';

  COUNT(*)
----------
         3

SQL>

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36589764
curious,  why was http:#36589556  chosen?  it does not work reliably to find spaces

please don't score with  "everybody wins",
you should only accept the posts that are helpful toward the question you asked
0
 

Author Comment

by:sikyala
ID: 36590418
I calculated a count of records with a value and records with a null value and total number of records that exist in the table. The number with a value plus the number with a null value equaled the total value in the table. The queries offered did work and returned no results. I used them all so I assigned points to them
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36590987
well I guess given that your specific situation was quirky (using implicit conversion on a date to check for string values)  then I guess that query "could" have worked.

but, it's not a valid solution for your question in general.
in fact, it's not even reliable for your specific situation  - it might have worked for your session, but isn't guaranteed to work in general

SQL> create table bib_holdings(publication_date date);

Table created.

SQL> insert into bib_holdings values (sysdate);

1 row created.

SQL> insert into bib_holdings values (sysdate-1);

1 row created.

SQL> insert into bib_holdings values (sysdate+1);

1 row created.

SQL> commit;

Commit complete.

SQL> Select count(*) from bib_holdings where publication_date like ' % ';

  COUNT(*)
----------
         3

SQL> select * from bib_holdings where (length(publication_date)-length(replace(publication_date,' ','')))>2;

no rows selected

SQL>

Open in new window

0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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