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

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?
sikyalaSenior Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
sdstuberCommented:
where publication_date like '% %';
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
sdstuberCommented:
where regexp_like(publication_date,' ')
0
 
sdstuberCommented:
where replace(publication_date,' ') != publication_date
or (replace(publication_date,' ')  is null and publication_date is not null)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
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
 
johnsoneSenior Oracle DBACommented:
My personal choice would be:

WHERE INSTR(PUBLICATION_DATE, ' ') > 0
0
 
x77Commented:
To find records with 3+ Spaces:

select *  
from YourTable
where (length(YourFld)-length(replace(YourFld,' ','')))>2;
0
 
sdstuberCommented:
http:#36589556  doesn't handle nulls properly

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


see  http:#36588423
0
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
sdstuberCommented:
>> 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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
sdstuberCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.