vbscript string INequality when one side is null other not

Question

First post here. Please feel free to let me know if I should post this somewhere else or whatever. :)

I've Googled for variations on "String Compare" and "String Inequality" and their results are useless. My problem is that I'm checking for string inequality and I'm getting surprising results when one side is null and the other isn't. Consider the following demonstration code:

TestStr "foo", "foo", "="
TestStr "foo", "bar", "<>"
TestStr Null, "foo", "<>"
TestStr "foo", Null, "<>"

Sub TestStr(left, right, expected)
    ' I actually don't care about this - it's here for reference
    If left = right Then
        WScript.Echo "left = right - expected " & expected
    Else
        WScript.Echo "left <> right - expected " & expected
    End If

    If left <> right Then
        WScript.Echo "left <> right - expected " & expected
    Else
        WScript.Echo "left = right - expected " & expected
    End If
    WScript.Echo ""
End Sub

Running this code produces the following output ("codified" so it isn't formatted):

left = right - expected =
left = right - expected =

left <> right - expected <>
left <> right - expected <>

left <> right - expected <>
left = right - expected <>

left <> right - expected <>
left = right - expected <>

As you can see, string inequality fails when one side is null and the other isn't - which I'm current experiencing. I know I can do what's below, but I'm wondering if I'm missing an easier way:

If (left = right) Then
Else
    ' Do inequality stuff here...
End If

[EDIT] I'm getting great feedback on why this is happening. Thanks folks! However, I'm also interested in how to avoid this.

[EDIT 2] What I'm hoping for is a simple way to know when the strings are different (thanks for the suggestion Ekkehard.Horner).

• If both are null, then one could argue that they aren't the same, but they're same enough for me.
• If one is null and the other is not, then they're different - pretty obviously.
• If both are not null, then they are the same if they're the exactly the same - including case sensitivity.

So for my purposes, something where "expected" and what actually happens in my demo code agree would be sufficient.

Answer 1

To illustrate Panayot's statement (+1) and make it easy to experiment:

  ' vbEmpty         0  Uninitialized (default)
  ' vbNull          1  Contains no valid data
  ' vbInteger       2  Integer subtype
  ' vbLong          3  Long subtype
  ' vbSingle        4  Single subtype
  ' vbDouble        5  Double subtype
  ' vbCurrency      6  Currency subtype
  ' vbDate          7  Date subtype
  ' vbString        8  String subtype
  ' vbObject        9  Object
  ' vbError        10  Error subtype
  ' vbBoolean      11  Boolean subtype
  ' vbVariant      12  Variant (used only for arrays of variants)
  ' vbDataObject   13  Data access object
  ' vbDecimal      14  Decimal subtype
  ' vbByte         17  Byte subtype
  ' vbArray      8192  Array
  Dim aData : aData = Array(Empty, Null, CInt(0), CLng(0), "", "a", Nothing)
  Dim sXfx  : sXfx  = Space(25)
  Dim k     : k     = 0
  Dim i, j
  For i = 0 To UBound(aData)
      For j = 0 To UBound(aData)
          WScript.Echo               _
               Right(1000 + k, 3)     _
             , Right(sXfx & toTypeStr(aData(i)), 25)   _
             , Left(toTypeStr(aData(j))& sXfx, 25)   _
             , compareX(aData(i), aData(j))
          k = k + 1
      Next
  Next

Function toTypeStr(vX)
  Dim aTmp : aTmp = Array(VarType(vX), TypeName(vX), "")
  Select Case aTmp(0)
    Case vbEmpty  : aTmp(2) = "<Empty>"
    Case vbNull   : aTmp(2) = "<Null>"
    Case vbString : aTmp(2) = """" & vX & """"
    Case vbObject : aTmp(2) = "<Object>"
    Case Else     : aTmp(2) = CStr(vX)
  End Select
  toTypeStr = Join(aTmp, ":")
End Function

Function compareX(vLHS, vRHS)
  Dim vEQ
 On Error Resume Next
  vEQ = vLHS = vRHS
  If Err.Number Then compareX = Err.Description : Exit Function : End If
 On Error GoTo 0
  Select Case True
    Case VarType(vEQ) = vbNull
      compareX = toTypeStr(vEQ) & " - can't compare with Null"
    Case VarType(vEQ) <> vbBoolean
      compareX = toTypeStr(vEQ) & " surprise"
    Case vEQ
      compareX = "equal"
    Case Else
      compareX = "not equal"
  End Select
End Function

output:

000           0:Empty:<Empty> 0:Empty:<Empty>           equal
001           0:Empty:<Empty> 1:Null:<Null>             1:Null:<Null> - can't compare with Null
002           0:Empty:<Empty> 2:Integer:0               equal
003           0:Empty:<Empty> 3:Long:0                  equal
004           0:Empty:<Empty> 8:String:""               equal
005           0:Empty:<Empty> 8:String:"a"              not equal
006           0:Empty:<Empty> 9:Nothing:<Object>        Object variable not set
007             1:Null:<Null> 0:Empty:<Empty>           1:Null:<Null> - can't compare with Null
008             1:Null:<Null> 1:Null:<Null>             1:Null:<Null> - can't compare with Null
009             1:Null:<Null> 2:Integer:0               1:Null:<Null> - can't compare with Null
010             1:Null:<Null> 3:Long:0                  1:Null:<Null> - can't compare with Null
011             1:Null:<Null> 8:String:""               1:Null:<Null> - can't compare with Null
012             1:Null:<Null> 8:String:"a"              1:Null:<Null> - can't compare with Null
013             1:Null:<Null> 9:Nothing:<Object>        Object variable not set
014               2:Integer:0 0:Empty:<Empty>           equal
015               2:Integer:0 1:Null:<Null>             1:Null:<Null> - can't compare with Null
016               2:Integer:0 2:Integer:0               equal
017               2:Integer:0 3:Long:0                  equal
018               2:Integer:0 8:String:""               not equal
019               2:Integer:0 8:String:"a"              not equal
020               2:Integer:0 9:Nothing:<Object>        Object variable not set
021                  3:Long:0 0:Empty:<Empty>           equal
022                  3:Long:0 1:Null:<Null>             1:Null:<Null> - can't compare with Null
023                  3:Long:0 2:Integer:0               equal
024                  3:Long:0 3:Long:0                  equal
025                  3:Long:0 8:String:""               not equal
026                  3:Long:0 8:String:"a"              not equal
027                  3:Long:0 9:Nothing:<Object>        Object variable not set
028               8:String:"" 0:Empty:<Empty>           equal
029               8:String:"" 1:Null:<Null>             1:Null:<Null> - can't compare with Null
030               8:String:"" 2:Integer:0               not equal
031               8:String:"" 3:Long:0                  not equal
032               8:String:"" 8:String:""               equal
033               8:String:"" 8:String:"a"              not equal
034               8:String:"" 9:Nothing:<Object>        Object variable not set
035              8:String:"a" 0:Empty:<Empty>           not equal
036              8:String:"a" 1:Null:<Null>             1:Null:<Null> - can't compare with Null
037              8:String:"a" 2:Integer:0               not equal
038              8:String:"a" 3:Long:0                  not equal
039              8:String:"a" 8:String:""               not equal
040              8:String:"a" 8:String:"a"              equal
041              8:String:"a" 9:Nothing:<Object>        Object variable not set
042        9:Nothing:<Object> 0:Empty:<Empty>           Object variable not set
043        9:Nothing:<Object> 1:Null:<Null>             Object variable not set
044        9:Nothing:<Object> 2:Integer:0               Object variable not set
045        9:Nothing:<Object> 3:Long:0                  Object variable not set
046        9:Nothing:<Object> 8:String:""               Object variable not set
047        9:Nothing:<Object> 8:String:"a"              Object variable not set
048        9:Nothing:<Object> 9:Nothing:<Object>        Object variable not set

As you can see from #001 and #007-#013, the result of an operation (here: =) applied to a Null value is always Null.

Update:

For the special case (Null or Strings only, both Null are considered equal):

  Dim aData : aData = Array(Null, "", "a")
  Dim sXfx  : sXfx  = Space(25)
  Dim k     : k     = 0
  Dim i, j
  For i = 0 To UBound(aData)
      For j = 0 To UBound(aData)
          WScript.Echo               _
               Right(1000 + k, 3)     _
             , Right(sXfx & toTypeStr(aData(i)), 25)   _
             , Left(toTypeStr(aData(j))& sXfx, 25)   _
             , CStr(compareStrNull(aData(i), aData(j)))
          k = k + 1
      Next
  Next

Function compareStrNull(vLHS, vRHS)
  Dim ntLHS : ntLHS = VarType(vLHS)
  Dim ntRHS : ntRHS = VarType(vRHS)
  Select Case True
'   If both are null, then one could argue that they aren't the same, but they're same enough for me.
    Case ntLHS = ntRHS And ntLHS = vbNull
      compareStrNull = True
'   If one is null and the other is not, then they're different - pretty obviously.
    Case ntLHS = vbNull Or ntRHS = vbNull
      compareStrNull = False
'   If both are not null, then they are the same if they're the exactly the same - including case sensitivity.
    Case ntLHS = ntRHS And ntLHS = vbString
      compareStrNull = vLHS = vRHS
    Case Else
      Err.Raise 4711, "compareStrNull", "surprise"
  End Select
End Function

output:

000             1:Null:<Null> 1:Null:<Null>             True
001             1:Null:<Null> 8:String:""               False
002             1:Null:<Null> 8:String:"a"              False
003               8:String:"" 1:Null:<Null>             False
004               8:String:"" 8:String:""               True
005               8:String:"" 8:String:"a"              False
006              8:String:"a" 1:Null:<Null>             False
007              8:String:"a" 8:String:""               False
008              8:String:"a" 8:String:"a"              True

Update II:

Simplified/Optimized version of the compare function:

Function compareStrNull02(vLHS, vRHS)
  compareStrNull02 = vLHS = vRHS                      ' native compare
  If VarType(compareStrNull02) = vbNull Then          ' at least one param is Null
     compareStrNull02 = VarType(vLHS) = VarType(vRHS) ' are both Null?
  End If
End Function

Dealing with arrays or objects needs further work.

Answer 2

I think you miss the base of Comparison Operators in VBScript. Comparing with Null always return Null, not boolean (True/False).

Update:

String comparison is case sensitive, but your script will compare String with Null in some cases, and your second question is how to simplify this task, right? In this case writing a function is a good idea.

Function CompStr(sVal1, sVal2)
    CompStr = False
    If VarType(sVal1) <> vbString Then Exit Function
    If VarType(sVal2) <> vbString Then Exit Function
    CompStr = sVal1 = sVal2
End Function

Answer 3

Try adding this as the first line:

if isnull (left) or isnull (right) then exit sub