Only return rows that have an empty field?

Question

I have a table where one of the fields can be empty. I'm trying to return only these rows where this field is empty. But I keep getting errors just doing WHERE field = "" ... WHERE field = '' ... WHERE field = null

Any ideas what I'm missing?

Cavie

What are you putting in the ...? – Anorthic

I think this illustrates three possible Where statements. – Idiomatic

Answer 1

In SQL, a completely empty field is said to be NULL. For NULL searches you do not use equals (=), but rather the special operator IS NULL:

 SELECT * FROM table WHERE field IS NULL

Access allows you to have not NULL empty fields if you allow empty strings, this is a bad idea as it makes it difficult to distinguish visual between a NULL value and a 0-length string, so I suggest you don't permit it in your database.

Homeostasis

Space-filled and zero length string are possible, so to cover all bases you can use Trim(field & "")="". – Idiomatic

I'll accept 0-length strings as empty, but personally I wouldn't consider a space-padded string empty. In any event, from the question I'm pretty sure the OPs issue is not identifying different kinds of empty strings, but not knowing the IS NULL operator. – Homeostasis

Answer 2

SELECT * 
  FROM MyTable 
 WHERE IIF(MyField = ' ', NULL, MyField) IS NULL;

UPDATE: here's a demonstration of how "ANSI padding" works in the Access Database Engine (ACE, Jet, whatever), which somehow seems to be necessary (surely every SQL product in the land works this way...?): just paste into any VBA (Access, Excel, Word, etc) or VB6 module and run (no references etc required): if it is true that a single space is equal to a zero-length string (ZLS) or an 'undetermined' number of spaces then you will see a list of Ys:

Sub Fundamentals()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim SQL As String
      SQL = _
      "SELECT IIF(SPACE(0) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(1) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(2) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(3) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(4) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(5) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(55) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(99) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(255) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(4321) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(54321) = SPACE(1), 'Y', 'N')," & vbCr & _
      "       IIF(SPACE(654321) = SPACE(1), 'Y', 'N');"

      .Execute SQL

      Dim rs
      Set rs = .Execute(SQL)
      MsgBox rs.GetString(, , vbCr)
    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

UPDATE2:

Certainly Jet/ACE doesn't pad fields to fixed length!

Incorrect. The Access Database has a fixed width text data type generally known as NCHAR(n) (though other synonym apply) that does indeed pad column values to fixed length...

What data type is NCHAR(10) in the Access table designer?

I don't it will show in the table designer thing correctly. The Access UI still lags behind the Jet 4.0 technology, there are many such omissions. I don't have Access installed at the moment -- perhaps someone could run the following code, open the .mdb in the Access UI and tell us...?

Sub AccessNChar()

  On Error Resume Next
  Kill Environ$("temp") & "\DropMe.mdb"
  On Error GoTo 0

  Dim cat
  Set cat = CreateObject("ADOX.Catalog")
  With cat
    .Create _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & _
        Environ$("temp") & "\DropMe.mdb"
    With .ActiveConnection

      Dim Sql As String

      Sql = "CREATE TABLE TestNChar (col1 NCHAR(10));"
      .Execute Sql

      Sql = "INSERT INTO TestNChar (col1) VALUES (SPACE(1));"
      .Execute Sql

      Sql = "SELECT LEN(col1) FROM TestNChar;"

      Dim rs
      Set rs = .Execute(Sql)

      MsgBox rs.GetString
    End With
    Set .ActiveConnection = Nothing
  End With
End Sub

Crispate

@Remou: it does indeed check for a ZLS or an undetermined number of spaces. – Crispate

Huh? How? It seems to me that it's checking for a single space and will return a ZLS or any other number of spaces if the initial IIf() condition does not match. And it's not going to use indexes, so seems to me like a very bad idea. – Enfeoff

@Davide-W-Fenton and @Remou: you both have MS-Access badges here on SO, this is fundamental stuff! Standard behaviour when comparing two strings for equality is end pad the shorter with space characters. Even if you weren't aware of this (shocking!) or had momentarily forgotten, surely it would be really easy for you to test. In a community such as this, I expect you to test my code anyhow, peer review and all that. But I suppose I'll have to post my proof as really easy to run code (assuming you have the courtesy to be bothered to run it!) – Crispate

I was not aware of this, and find it a not useful thing. I have never run onto it because I don't allow spaces to be stored in my data tables. If all databases behave this way, I'd say they are wrong, since there is no index-using way to search for a field with a single space in it. And certainly, if you're allowing the storage of spaces in your data field, it must mean something, and one space must mean something different from two. Call me an idiot if you like, but this seems like an artifact of ancient dBase-style data storage. Certainly Jet/ACE doesn't pad fields to fixed length! – Enfeoff

@David-W-Fenton: "Call me an idiot if you like, but this seems like an artifact of ancient dBase-style data storage" -- it's artifact of SQL-92, itself a little long in the tooth. I wouldn't call you an idiot but would point out that "embracing the new reality" is something you seem to have a problem with. – Crispate

@David-W-Fenton: "I don't allow spaces to be stored in my data tables" -- I assume you don't disallow spaces in all circumstances e.g. a single space between two words is OK, right? :) I habitually add a CHECK constraint to all text columns to disallow: 1) zero-length space, 2) a leading space, 3) a trailing space, 4) two consecutive spaces. – Crispate

What data type is NCHAR(10) in the Access table designer? – Enfeoff

@David-W-Fenton: Yes, you will need SQL DDL in ANSI-92 Query Mode to create such database objects. You may even need OLE DB to detect their presence and display their properties. However, this does not mean they do not exist and IMO any self respecting Access professional should be able to at least describe the problems they were designed to solve. – Crispate

@David-W-Fenton: This answer further updated with code to create a new .mdb with a table comprising an NCHAR(10) column. Perhaps you could run the code, open the .mdb in Access and tell us what you see? TIA. – Crispate

I'm an Access developer, not a Jet/ACE developer. My point in asking the question is to raise the issue of WHY Jet/ACE supports NCHAR(10). My guess is that it is for compatibility with outside standards that don't have any meaning to an Access developer. I can't think why I'd ever need it. Can you come up with a reason why I should bother to learn about a feature that I won't ever need to use, one that is introduced into the database engine for compatibility with things outside my working environment? – Enfeoff

@David-W-Fenton: "WHY Jet/ACE supports NCHAR(10)" -- it's a fixed width data type for fixed-width data, which is extremely common. Most of the industry standard identifiers I encounter are fixed-width e.g. so far to day I've used ISO 3166-1 alpha-3 country codes, ISO 4217 currency codes and industry SIC codes. – Crispate

@David-W-Fenton: "Can you come up with a reason why I should bother to learn about a feature that I won't ever need to use" -- I actually said you should have knowledge of the problems they were designed to solve i.e. so you don't repeat the same flaws in your clients' products. What's your reasoning for having knowledge of only a subset of a language's data types? – Crispate

I only ever use Jet/ACE via Access, and since Access doesn't expose these data types as directly usable, I am not going to use them. I really don't fathom why a data format that is variable length would benefit from having the fixed-length option. But maybe I don't even understand what it means (I'm assuming fixed-width means fixed width, padded with spaces, a la xBase c. 1988). – Enfeoff

Answer 3

What do you mean by errors?

But, if you want to get just those rows where the fields are empty, attempt something like thus:

SELECT * FROM MyTable WHERE LTRIM(RTRIM(ISNULL(MyField, ''))) = ''

Kneepan

For Access you would use Nz rather than ISNULL(MyField, ''). IsNull does not take more than one argument. – Idiomatic

That's true. Of course, I don't use access, so I tend to forget the specifics on how Access tortures SQL. – Kneepan

All database engines have variations in their SQL dialect. -1 for posting a non-Access answer. – Enfeoff

good solution, by pre-qualifying a NULL value, turning into an empty string, then trimming both sides guarantees he will be getting a character string for the final = '' test. – Clearsighted

Answer 4

Does your query have three WHERE clauses? If so, change the second two to OR

Stypsis

Answer 5

Yeah, I was facing the same But finally I tried the following mysql query it worked and saved me.

  SELECT * from your_table_name WHERE field_name IS NULL;

Go ahead with this query it will select only the rows which are empty.

Ganglion