Expressions

SQL queries can contain expressions. Expressions can contain literals and references to columns used by a query. There are several types of expressions with the most frequently used types being generic expressions, numeric expressions, string expressions, date expressions, and Boolean expressions.

 

Generic Expressions

 

Generic expressions are expressions that can return values of any type. Generic expressions can include the following functions:

 

Function

Comments

Coalesce(value, value[, value ...])

Returns the first value not equal to NULL.

IIf(condition, value, value)

Evaluates the condition and returns the first value if it is True, and the second value otherwise.

Max(v1, v2[, v3 ...])

Computes the maximum value in the list of values. Accepts any number of values of any type. NULL values are ignored. If all values are NULL, returns NULL.

Min(v1, v2[, v3 ...])

Computes the minimum value in the list of values. Accepts any number of values of any type. NULL values are ignored. If all values are NULL, returns NULL.

NullIf(value, value)

Returns NULL if the first value is equal to the second value, and the first value otherwise.

TypeName(value)

Returns the name of a value type.

VarType(value)

Returns an integer value representing a value type.

 

Numeric Expressions and Functions

 

Numeric expressions are expressions that return numeric values. Numeric expressions can include numeric literals. Numeric expressions can include the following arithmetic operators (in order of increasing priority):

 

+

Addition

-

Subtraction

/

Division

*

Multiplication

Div

Integer division, equivalent to VBScript " \ "

Mod

Modulo. Performs a division, retaining only the remainder. [Note: this is in line with VB.NET but different from VBScript, which first converts floating point numbers to integers via rounding and then performs the division.]

^

Exponentiation

 

Numeric expressions can be used with the following functions:

 

Function

Comments

Abs(number)

Returns the absolute value of a number.

Acos(number)

Returns the arc cosine of a number.

Asin(number)

Returns the arc sine of a number.

Atn(number)

Returns the arc tangent of a number.

Atn2(y, x)

Returns the arc tangent of y/x.

Ceil(x)

Returns the smallest integer that is greater than or equal to x.

Cos(number)

Returns the cosine of a number.

Deg2Rad(x)

Converts x from degrees to radians.

Exp(number)

Returns the exponent of a number.

Fix(number)

Converts a fractional number to an integer number rounding towards zero.

Floor(x)

Returns the largest integer that is less than or equal to x.

Int(number)

Converts a fractional number to an integer number rounding down.

Log(number)

Returns the natural logarithm of a number.

Log2(x)

Returns the binary logarithm of x.

Log10(x)

Returns the decimal logarithm of x.

Pow(x, y)

Returns the value of x raised to the power of y.

Rad2Deg(x)

Converts x from radians to degrees.

Rgb(red, green, blue)

Composes red, green, and blue components of a color into an integer number.

Rnd

Returns a random number between 0 and 1. Used without parentheses.

Round(number[, decimals])

Rounds a fractional number to a given number of decimal digits and returns the result. If the number of decimal digits is omitted, it is assumed to be 0.

Sgn(number)

Returns the sign of a number.

Sin(number)

Returns the sine of a number.

Sqr(number)

Returns the square root of a number.

Tan(number)

Returns the tangent of a number.

 

String Expressions

 

String expressions are expressions that return string values. String expressions can include string literals. String expressions can be concatenated with ||, +, or & operators. String expressions can be used with the following functions:

 

Function

Comments

Bit_Length(string)

Returns the length of a string in bits (an ANSI character is 8 bit wide and a Unicode character is 16 bit wide). Also supports binary values.

Char_Length(string)

Returns the length of string in characters.

Character_Length(string)

Same as Char_Length(string).

Chr(number)

Returns a character with the given numeric value.

FormatCurrency(number[, decimals[, leading[, parens[, groups]]]])

Prints a currency value into a string using the given number of decimal digits (system-defined by default). The third parameter specifies whether a leading zero is included (-1) or excluded (0). The fourth parameter specifies whether negative values are printed in parentheses (-1) or not (0). The fifth parameter specifies whether the numbers are grouped (-1) or not (0). The third, fourth, and fifth parameters can be set to be taken from the regional settings of the current user (-2, the default).

FormatDateTime(date[, format])

Prints a date into a string using either the default format (0), long date format (1), short date format (2), long time format (3), or short time format (4, uses 24 hours).

FormatNumber(number[, decimals[, leading[, parens[, groups]]]])

Prints a number into a string using the given number of decimal digits (system-defined by default). The third parameter specifies whether a leading zero is included (-1) or excluded (0). The fourth parameter specifies whether negative values are printed in parentheses (-1) or not (0). The fifth parameter specifies whether the numbers are grouped (-1) or not (0). The third, fourth, and fifth parameters can be set to be taken from the regional settings of the current user (-2, the default).

FormatPercent(number[, decimals[, leading[, parens[, groups]]]])

Prints a percentage into a string using the given number of decimal digits (system-defined by default). The third parameter specifies whether a leading zero is included (-1) or excluded (0). The fourth parameter specifies whether negative values are printed in parentheses (-1) or not (0). The fifth parameter specifies whether the numbers are grouped (-1) or not (0). The third, fourth, and fifth parameters can be set to be taken from the regional settings of the current user (-2, the default).

Hex(number)

Prints an integer number into a string using hexadecimal notation.

InStr(string, string[, start[, case]])

Returns the position of the first occurrence of the second string in the first string, or 0 if there are no occurrences. The third parameter specifies the starting position of a search (1 by default). The fourth parameter specifies whether the search is case-sensitive (0, the default), or case-insensitive (1).

InStrRev(string, string[, start[, case]])

Returns the position of the last occurrence of the second string within the first string, or 0 if there are no occurrences. The third parameter specifies the starting position of a search (1 by default). The fourth parameter specifies whether the search is case-sensitive (0, the default), or case-insensitive (1).

Left(string, length)

Returns the given number of starting characters in a string.

Len(string)

Same as Char_Length(string).

LCase(string)

Converts all string characters to lower case and returns the result.

Lower(string)

Same as LCase(string).

LTrim(string)

Trims leading white space characters from a string and returns the result.

Mid(string, start[, length])

Returns the portion of a string starting with a given character (one-based). If the length parameter is omitted, the function returns the rest of the string.

Oct(number)

Prints an integer number into a string using octal notation.

Octet_Length(string)

Returns the length of a string in bytes (an ANSI character is 1 byte wide and a Unicode character is 2 byte wide). Also supports binary values.

RegExp(string, pattern, substitute)

Transforms a string with regular expressions and returns the result.

Replace(string, what, with[, start[, count[, case]]])

Replaces all occurrences of one substring in a string with another substring, and returns the result. The fourth parameter specifies the starting position of a search (1 by default). The fifth parameter specifies the maximum number of substitutions to perform and can be set to -1 (the default) to perform all possible substitutions. The sixth parameter specifies whether the search is case-sensitive (0, the default), or case-insensitive (1).

Right(string, length)

Returns the given number of trailing characters in a string.

RTrim(string)

Trims trailing white space characters from a string and returns the result.

SoundsLike(string, string)

Returns True if strings sound like each other, and False otherwise. Uses Soundex algorithm for English language.

Space(number)

Returns a string with the given number of spaces.

StrComp(string, string[, case])

Compares two strings and returns -1 if the first string is less than the second string, 1 if the first string is greater than the second string, and 0 if the strings are equal. The third parameter specifies whether the comparison is case-sensitive (0, the default), or case-insensitive (1).

String(number, character)

Returns a string with the given number of given characters. The second parameter can be either a string (that gets reduced to the first character), or a number (that gets converted to a string with a Chr function).

StrReverse(string)

Reverses a string and returns the result.

Token(string[, index[, delimiters]])

Returns a token with the given index (one-based). The third parameter is a string that contains token delimiters. If the third parameter is omitted, tokens are separated with white space characters.

UCase(string)

Converts all string characters to upper case and returns the result.

Upper(string)

Same as UCase(string).

 

Date Expressions

 

Date expressions are expressions that return date values. Date expressions can include date literals. Date expressions can be used with the following functions:

 

Function

Comments

Current_Date

Same as Date. Used without parentheses.

Current_Time

Same as Time. Used without parentheses.

Current_TimeStamp

Same as Time. Used without parentheses.

Date

Returns the current date. Used without parentheses.

DateAdd(interval, number, date)

Increases or decreases a date by the given value in the given time interval, and returns the result. The first parameter is a string that specifies which interval to increase ("yyyy" for year, "q" for quarter, "m" for month, "y" for day of year, "d" for day of month, "w" for weekday, "ww" for week of year, "h" for hour, "n" for minute, and "s" for second).

DateDiff(interval, date, date[, start[, week]])

Returns the number of time intervals between two dates. The first parameter is a string that specifies the time interval. The fourth parameter is the numeric index of a day that starts the week (0 for the default start, 1 for Sunday, 2 for Tuesday, and so on). The fifth parameter is a number that specifies the method used to determine the first week in a year (0 for the default method, 1 to use the week that contains the 1st of January, 2 to use the first week that contains at least 4 days in the new year, 3 to use the first week that is entirely in the new year).

DatePart(interval, date[, start[, week]])

Returns the value of a specified time interval in a date. The first parameter is a string that specifies the time interval. The third parameter is the numeric index of a day that starts the week. The fourth parameter is a number that specifies the method used to determine the first week in a year.

DateSerial(year, month, day)

Composes a date without a time.

DateValue(value)

Converts a value to a date without a time ignoring hour, minute, and second information.

Day(date)

Returns the day of month of a date (1 to 31).

Hour(date)

Returns the hour of a date (0 to 23).

Minute(date)

Returns the minute of a date (0 to 59).

Month(date)

Returns the month of a date (1 to 12).

MonthName(month[, abbreviate])

Returns the name of a month (1 to 12). The second parameter switches between abbreviated names (True) and full names (False, the default).

Now

Same as Time. Used without parentheses.

Second(date)

Returns the second of a date (0 to 59).

Time

Returns the current date and time. Used without parentheses.

Timer

Returns the number of seconds since 12:00 AM. Used without parentheses.

TimeSerial(hour, minute, second)

Composes the time portion of a date.

TimeValue(value)

Converts a value to the time portion of a date ignoring year, month, and day information.

Weekday(date[, start])

Returns the weekday of a date (1 to 7). The second parameter is the numeric index of a day that starts the week (0 for the default start, 1 for Sunday, 2 for Tuesday, and so on).

WeekdayName(day[, abbreviation[, start]])

Returns the name of a weekday (1 to 7). The second parameter switches between abbreviated names (True) and full names (False, the default). The third parameter is the numeric index of a day that starts the week.

Year(date)

Returns the year of a date.

 

Boolean Expressions

 

Boolean expressions are expressions that return Boolean values. Boolean expressions can include Boolean literals, such as True and False, or Yes and No. Boolean expressions can include the following comparison operators:

 

>

Greater than

<

Less than

<>

Not equal to

>=

Greater than or equal to

<=

Less than or equal to

=

Equal to

 

When applied to strings, the above operators work from the leftmost to the rightmost character, comparing characters in alphabetical order (typically, A is less than B, B is less than C, digits are less than letters, upper case letters are less than lower case letters, etc.)

 

Boolean expressions can also include the following Boolean operators:

 

And

Logical AND (True if both operands are True)

Or

Logical OR (True if either operand is True)

Not

Logical NOT (True if the operand is False)

Eqv

Logical equivalence (True if both operands are either True or False)

Imp

Logical implication (True unless the first operand is True and the second operand is False)

Xor

Logical XOR (True if one of the operands is True, and another is False)

 

Conversion Functions

 

The expressions can also include the following functions that convert values from one type to another:

 

Function

Comments

BinaryToHex(value)

Converts a typed (for example, Geom) or untyped binary value to a text value, printing each byte using hexadecimal notation.

CBool(value)

Converts a value to a Boolean.

CByte(value)

Converts a value to a 8 bit unsigned integer number (byte).

CCoordSys(value)

Converts a value to a coordinate system.

CCur(value)

Converts a value to a currency.

CDate(value)

Converts a value to a date.

CDbl(value)

Converts a value to a double precision floating-point number.

CGeom (value)

Converts a value to a geometric object of type Geometry.

CGeomSDE(value)

Converts given data to geometric object of type Geometry (SDE).

CGeomSHP(value)

Converts given data to geometric object of type Geometry (SHP).

CGeomWKB(value)

Converts given data to geometric object of type Geometry (WKB).

CInt(value)

Converts a value to a 32 bit integer number.

CLng(value)

Converts a value to a 32 bit integer number (same as CInt).

CSng(value)

Converts a value to a single precision floating-point number.

CStr(value)

Converts a value to a string.

HexToBinary(value)

Converts a text value in the hexadecimal notation format used by the BinaryToHex function to an untyped binary value.

 

The BinaryToHex and HexToBinary functions are used to convert binary data to text format and vice versa. They are especially useful for storing binary data within data sources such as DBF files that do not support binary data columns.

 

Lookup Values and CStr and CAST

 

Invoking CStr on a lookup value or using CAST to convert the value to a string returns the descriptive name of the lookup value.

 

Example

 

We have a table T with a lookup column Region with values East and West. We want to select all records with the value of Region being East. We can do this with the following query:

 

SELECT * FROM [T] WHERE CStr([Region]) = "East";

 

Example

 

We have a drawing D and we want to select all areas it contains. We can do this with the following query:

 

SELECT * FROM [D] WHERE CAST([Type (I)] AS TEXT) = "Area";

 

See Also

 

Data Types

Regular Expressions

Spatial Extensions

Geocoding Extensions

Raster Extensions

 

Historical Note

 

images\img_boole.gif

The term "Boolean" takes its name from George Boole (1815 -1864), one of the great mathematicians of the nineteenth century and the inventor of mathematical logic and Boolean algebra, a systematic means of writing and manipulating logical concepts using mathematical symbols. Boolean algebra is the foundation of digital computer architecture.

 

Boole wrote fifty papers, two textbooks and two major volumes of work on mathematical logic and was a professor at Queen's College in Cork despite never having attended college or earning a degree. He was entirely self-taught. The son of a poor London shoemaker, Boole was encouraged by his father to pursue an early interest in learning. By age 12 he had mastered Latin and Greek and later turned his attention to mathematics. He died at 49 from an illness apparently incurred as a result of giving a lecture in soaking wet clothes after walking two miles through the rain. His untimely death interrupted a very productive career. One wonders what marvels the world lost for lack of an umbrella.

 

In English, George Boole's name is pronounced with a silent "e" to rhyme with "tool" or "cool".