Search code examples
sqlms-access

How is the LIKE Function dealing with empty fields?

I want to extract a specific part of a String, if the structure of that string is similar to a given example. Each element works if tested solo, however if i try the WHERE clause as seen below its not working. Is it possible that LIKE cant handle empty fields that well? Or am I missing something? I’m using MS Access, therefore the Wildcards in LIKE are ‘?’

The Select Statement should grab a substring of the row “endpoint1”, from the given position to the next “:”

The WHERE Statement should only allow two different formats of that given substring

  1. ?_????_??
  2. ?_????_?

SQL:

SELECT
  Mid(PLL.Endpoint1,7,(InStr(Mid(PLL.Endpoint1,7,10),":")-1))
FROM
    [Test] AS PLL
WHERE 
    Mid(PLL.Endpoint1, 7, InStr(Mid(PLL.Endpoint1, 7, 10), ":") - 1) Like '?_????_??'
    OR Mid(PLL.Endpoint1, 7, InStr(Mid(PLL.Endpoint1, 7, 10), ":") - 1) Like '?_????_?'
Solution

  • Issue is not with LIKE comparison.

    If field is Null then InStr() will return Null. Mid() errors if result of InStr() is Null because its position arguments must be numeric. Could use Nz() to provide alternate value:

    InStr(Mid(Nz(PLL.Endpoint1, ""), 7, 10), ":")

    Now if your full expression returns Null, record will not be retrieved since comparing to Null returns Null. Null is not True therefore the record will be excluded same as False.