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

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

http:#36589556  doesn't handle nulls properly

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


see  http:#36588423

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

>> 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

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

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>

Select allOpen in new window

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

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

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>

Select allOpen in new window