SQL function equivalent

  • Thread starter Thread starter Chris M via AccessMonster.com
  • Start date Start date
C

Chris M via AccessMonster.com

I'm using the IsEmpty function in an Access Procedure and am converting the
database to SQL Server. I would like to know what the equivalent for IsEmpty
is in SQL.
 
Chris,

From the SQL Server Books Online:
IS [NOT] NULL
Determines whether or not a given expression is NULL.

Syntax
expression IS [ NOT ] NULL

Arguments
expression

Is any valid Microsoft® SQL ServerT expression.

NOT

Specifies that the Boolean result be negated. The predicate reverses its
return values, returning TRUE if the value is not NULL, and FALSE if the
value is NULL.

Result Types
Boolean

Return Code Values
If the value of expression is NULL, IS NULL returns TRUE; otherwise, it
returns FALSE.

If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it
returns TRUE.

Remarks
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather
than comparison operators (such as = or !=). Comparison operators return
UNKNOWN if either or both arguments are NULL.

Examples
This example returns the title number and the advance amount for all books
in which either the advance amount is less than $5,000 or the advance is
unknown (or NULL). Note that the results shown are those returned after
Example C has been executed.

USE pubs
SELECT title_id, advance
FROM titles
WHERE advance < $5000 OR advance IS NULL
ORDER BY title_id
Here is the result set:

title_id advance
-------- --------------------------
MC2222 0.0000
MC3026 NULL
PC9999 NULL
PS2091 2275.0000
PS3333 2000.0000
PS7777 4000.0000
TC4203 4000.0000

(7 row(s) affected)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks Graham, I'll check it out.
Chris,

From the SQL Server Books Online:
IS [NOT] NULL
Determines whether or not a given expression is NULL.

Syntax
expression IS [ NOT ] NULL

Arguments
expression

Is any valid Microsoft® SQL ServerT expression.

NOT

Specifies that the Boolean result be negated. The predicate reverses its
return values, returning TRUE if the value is not NULL, and FALSE if the
value is NULL.

Result Types
Boolean

Return Code Values
If the value of expression is NULL, IS NULL returns TRUE; otherwise, it
returns FALSE.

If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it
returns TRUE.

Remarks
To determine if an expression is NULL, use IS NULL or IS NOT NULL rather
than comparison operators (such as = or !=). Comparison operators return
UNKNOWN if either or both arguments are NULL.

Examples
This example returns the title number and the advance amount for all books
in which either the advance amount is less than $5,000 or the advance is
unknown (or NULL). Note that the results shown are those returned after
Example C has been executed.

USE pubs
SELECT title_id, advance
FROM titles
WHERE advance < $5000 OR advance IS NULL
ORDER BY title_id
Here is the result set:

title_id advance
-------- --------------------------
MC2222 0.0000
MC3026 NULL
PC9999 NULL
PS2091 2275.0000
PS3333 2000.0000
PS7777 4000.0000
TC4203 4000.0000

(7 row(s) affected)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
I'm using the IsEmpty function in an Access Procedure and am converting
the
database to SQL Server. I would like to know what the equivalent for
IsEmpty
is in SQL.
 
I can't think of any situation in which you would need IsEmpty in SQL,
Chris. The IsEmpty function returns True only when the argument is an
uninitialized Variant. It does not return True when the argument is Null, or
a zero-length string. The following test code may help to illustrate the
point ...

Public Sub TestSub()

Dim varTest As Variant
Dim rst As ADODB.Recordset

Debug.Print "Before assignment"
Debug.Print "Is variable empty?", IsEmpty(varTest)
Debug.Print "Is variable null?", IsNull(varTest)

varTest = Null
Debug.Print "After assignment"
Debug.Print "Is variable empty?", IsEmpty(varTest)

Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT TestText FROM tblTest WHERE TestText IS NULL"
.Open
Debug.Print "Null field"
Debug.Print "Records found?", (.BOF = False And .EOF = False)
Debug.Print "Is field null?", IsNull(.Fields("TestText"))
Debug.Print "Is field empty?", IsEmpty(.Fields("TestText"))
.Close
Debug.Print "Zero-length field"
.Source = "SELECT TestText FROM tblTest WHERE TestText = ''"
.Open
Debug.Print "Records found?", (.BOF = False And .EOF = False)
Debug.Print "Is field empty?", IsEmpty(.Fields("TestText"))
.Close
End With

End Sub

Results in Immediate window ...

testsub
Before assignment
Is variable empty? True
Is variable null? False
After assignment
Is variable empty? False
Null field
Records found? True
Is field null? True
Is field empty? False
Zero-length field
Records found? True
Is field empty? False
 
Back
Top