Operator | Meaning |
---|---|
& | bitwise AND |
~ | bitwise NOT |
| | bitwise OR |
^ | bitwise exclusive OR |
BIT | TINYINT | SMALLINT |
INTEGER | BIGINT | UTINYINT |
USMALLINT | UINTEGER | UBIGINT |
Bitwise Operator | Description and Syntax | Values Returned | Example |
---|---|---|---|
AND | The bitwise AND operator performs a bitwise logical AND operation between two operands. AND compares two bits and assigns a value equal to 1 to the result only if the values of both the bits are equal to 1. Otherwise, the bit in the result is set to 0. expression & expression Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation. | In a bitwise AND operation involving operands of different integer data types, the argument of the smaller data type is converted to the larger data type or to the data type that is immediately larger than the larger of the two operands. If any of the operands involved in a bitwise AND operation is signed, then the resultant value is also signed. | The & operator can be used in conjunction with the IF function to find out whether a table is a system table or a user-defined table. select Xf$Name, IF(Xf$Flags & 16 = 16, 'System table','User table') from X$File |
NOT | The bitwise NOT operator inverts the bit values of any variable and sets the corresponding bit in the result. ~ expression Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation. The tilde (~) cannot be used as part of a user-defined name. | The bitwise NOT operator returns the reverse of its single operand of the integer data type. All ones are converted to zeros, and all zeros are converted to ones. | The following query performs the complement operation on a numeric literal: SELECT ~12 The result is -13. The result is negative because the complement operator complements the sign bit also. |
OR | The bitwise OR operator performs a bitwise logical OR operation between two operands. OR compares two bits and assigns a value equal to 1 to the result if the values of either or both the bits are equal to 1. If neither bit in the input expressions has a value of 1, the bit in the result is set to 0. The OR operator can take only numeric values as its operands. expression | expression Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation. | In a bitwise OR operation involving operands of different integer data types, the argument of the smaller data type is converted to the larger data type or to the data type that is immediately larger than the larger of the two operands. If any of the operands involved in a bitwise OR operation is signed, then the resultant value will also be signed. | The following could obtain a list of foreign key and primary constraints: select B.Xf$Name "Table name", C.Xe$Name "Column name", IF (Xi$Flags & 8192 = 0, 'Primary key', 'Foreign key') "Key type" from X$Index A, X$File B, X$Field C where (A.Xi$Flags & (16384 | 8192)) > 0 AND A.Xi$File = B.Xf$Id AND A.Xi$Field = C.Xe$Id |
OR (exclusive) | The bitwise exclusive OR operator performs a bitwise logical exclusive OR operation between two operands. Exclusive OR compares two bits and assigns a value equal to 0 to the result if the values of both the bits are either 0 or 1. Otherwise, this operator sets the corresponding result bit to 1. expression ^ expression Expression is any valid expression containing the integer data type, which is transformed into a binary number for the bitwise operation. The circumflex (^) cannot be used as part of a user-defined name. | In a bitwise exclusive OR operation involving operands of different integer data types, the argument of the smaller data type is converted to the larger data type or to the data type that is immediately larger than the larger of the two operands. If any of the operands involved in a bitwise exclusive OR operation is signed, then the resultant value is also signed. | The following SQL query performs the exclusive OR on two numeric literals: SELECT 12 ^ 8 The result is 4. |
A | B | A & B | A | B | A ^ B | ~ A |
---|---|---|---|---|---|
0 | 0 | 0 | 0 | 0 | 1 |
0 | 1 | 0 | 1 | 1 | 1 |
1 | 0 | 0 | 1 | 1 | 0 |
1 | 1 | 1 | 1 | 0 | 0 |
Function | Description |
---|---|
ASCII (string) | Returns a numeric value for the left most character of string. The value is the position of the character in the database code page. See also UNICODE function. |
BIT_LENGTH (string) | Returns the length in bits of string |
CHAR (code) | Returns a single-character string where the code argument selects the character from the database code page. The argument must be an integer value. See also NCHAR function. |
CHAR_LENGTH (string) | Returns the number of bytes in string. All padding is significant for CHAR and NCHAR string. |
CHAR_LENGTH2 (string) | Returns the number of characters in string. All padding is significant for CHAR and NCHAR string. A value less than the size of the string may be returned if the data contains double-byte characters. |
CHARACTER_LENGTH (string) | Same as CHAR_LENGTH. |
CONCAT (string1, string2) | Returns a string that results from combining string1 and string2. |
ISNUMERIC(string) | Returns 1 (TRUE) if the string value can be evaluated as a numeric value. Otherwise returns 0 (FALSE). |
LCASE or LOWER (string) | Converts all upper case characters in string to lower case. |
LEFT (string, count) | Returns the left most count of characters in string. The value of count is an integer. |
LENGTH (string) | Returns the number of characters in string. Trailing spaces are counted in a VARCHAR, NVARCHAR, LONGVARCHAR, or NLONGVARCHAR string. Trailing NULLs are counted in a CHAR, NCHAR, LONGVARCHAR, or NLONGVARCHAR string. The string termination character is not counted. When ANSI_PADDING = OFF, trailing NULLs are treated the same as trailing spaces and are not counted in the length of a CHAR column. |
LOCATE (string1, string2 [, start ]) | Returns the starting position of the first occurrence of string1 within string2. The search within string2 begins at the first character position unless you specify a starting position (start). The search begins at the starting position you specify. The first character position in string2 is 1. The string1 is not found, the function returns the value zero. |
LTRIM (string) | Returns the characters of string with leading blanks removed. All padding is significant for CHAR and NCHAR string. |
NCHAR (code) | Returns a single-character wide string where the code argument is a Unicode codepoint value. The argument must be an integer value. See also CHAR function. |
OCTET_LENGTH (string) | Returns the length of string in octets (bytes). All padding is significant for CHAR and NCHAR string. |
POSITION (string1, string2) | Returns the position of string1 in string2. If string1 does not exist in string2, a zero is returned. |
REPLACE (string1, string2, string3) | Searches string1 for occurrences of string2 and replaces each with string3. Returns the result. If no occurrences are found, string1 is returned. |
REPLICATE (string, count) | Returns a character string composed of string repeated count times. The value of count is an integer. |
REVERSE(string) | Returns a character string with the order of the characters reversed. Note that leading spaces in any string types are considered as significant, unlike trailing spaces which are not considered as significant. See Examples for an example. |
RIGHT (string, count) | Returns the right most count of characters in string. The value of count is an integer. |
RTRIM (string) | Returns the characters of string with trailing blanks removed. When ANSI_PADDING = OFF, trailing NULLs are treated the same as trailing spaces and are removed from a CHAR column value. |
SOUNDEX (string) | Converts an alpha string to a four character code to find similar sounding words or names. Returns a four character (SOUNDEX) code to evaluate the similarity of two strings, usually a name. Note: Conforms to the current rule set for the official implementation of Soundex used by the United States Government. |
SPACE (count) | Returns a character string consisting of count spaces. |
STUFF (string1, start, length, string2) | Returns a character string where length characters in string1 beginning at position start have been replaced by string2. The values of start and length are integers. |
SUBSTRING (string1, start, length) | Returns a character string derived from string1 beginning at the character position specified by start for length characters. The start value can be any number. The first position of string1 is 1. A start value of 0 or a negative number is considered left of the first position. Length cannot be negative. |
UCASE or UPPER (string) | Converts all lower case characters in string to upper case. |
UNICODE (string) | Returns the Unicode codepoint value for the left most character of string. See also ASCII function. |
Function | Description |
---|---|
ABS (numeric_exp) | Returns the absolute (positive) value of numeric_exp. |
ACOS (float_exp) | Returns the arc cosine of float_exp as an angle, expressed in radians. |
ASIN (float_exp) | Returns the arc sine of float_exp as an angle, expressed in radians. |
ATAN (float_exp) | Returns the arc tangent of float_exp as an angle, expressed in radians. |
ATAN2 (float_exp1, float_exp2) | Returns the arc tangent of the x and y coordinates, specified by float_exp1 and float_exp2, respectively, as an angle, expressed in radians. |
CEILING (numeric_exp) | Returns the smallest integer greater than or equal to numeric_exp. |
COS (float_exp) | Returns the cosine of float_exp, where float_exp is an angle expressed in radians. |
COT (float_exp) | Returns the cotangent of float_exp, where float_exp is an angle expressed in radians. |
DEGREES (numeric_exp) | Returns the number of degrees corresponding to numeric_exp radians. |
EXP (float_exp) | Returns the exponential value of float_exp. |
FLOOR (numeric_exp) | Returns the largest integer less than or equal to numeric_exp. |
LOG (float_exp) | Returns the natural logarithm of float_exp. |
LOG10 (float_exp) | Returns the base 10 logarithm of float_exp. |
MOD (integer_exp1, integer_exp2) | Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. |
PI() | Returns the constant value Pi as a floating point value. |
POWER (numeric_exp, integer_exp) | Returns the value of numeric_exp to the power of integer_exp. |
RADIANS (numeric_exp) | Returns the number of radians equivalent to numeric_exp degrees. |
RAND (integer_exp) | Returns a random floating-point value using integer_exp as the optional seed value. |
ROUND (numeric_exp, integer_exp) | Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| (absolute value of integer_exp) places to the left of the decimal point. |
SIGN (numeric_exp) | Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned. |
SIN (float_exp) | Returns the sine of float_exp, where float_exp is an angle expressed in radians. |
SQRT (float_exp) | Returns the square root of float_exp. |
TAN (float_exp) | Returns the tangent of float_exp, where float_exp is an angle expressed in radians. |
TRUNCATE (numeric_exp, integer_exp) | Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| (absolute value) places to the left of the decimal point. |
Function | Description |
---|---|
CURDATE() | Returns the current local date in the format 'yyyy-mm-dd'. Uses the local clock date by default. If SET TIME ZONE has been called, then the value of CURDATE() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE. |
CURRENT_DATE() | Returns the current UTC date in the format 'dd/mm/yyyy'. |
CURTIME() | Returns the current local time in the format 'hh:mm:ss'. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of CURTIME() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE. |
CURRENT_TIME() | Returns the current UTC time in the format 'hh:mm:ss'. |
CURRENT_TIMESTAMP() | Returns the current UTC date and time in the format 'yyyy-mm-dd hh:mm:ss.mmm'. |
DATEADD(datepart, interval, date_exp) | Returns a new DATETIME value by adding an interval to a date. For example, a datepart day, an interval of 11, and a date_exp of January 26, 2020 returns February 6, 2020. Datepart specifies the part of the date to which interval is added and must be one of the following values: •year •quarter •month •day •dayofyear •week •hour •minute •second •millisecond Interval specifies a positive or negative integer value used to increment datepart. If interval contains a fractional portion, the fraction part is ignored. Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME. |
DATEDIFF(datepart, start, end) | Returns an integer for the difference between the two dates. The integer is the number of date and time boundaries crossed between the two dates. For example, table mytest has two columns,col1 and col2, both of which are DATETIME. The value in col1 is 2000-01-01 11:11:11.234 and the value in col2 is 2004-09-11 10:10:10.211. The following SELECT statement returns 56, because that is the difference in months between col1 and col2: SELECT DATEDIFF(month, col1, col2) as Month_Difference FROM mytest Datepart specifies the part of the date on which to calculate the difference and must be one of the following values: •year •quarter •month •day •dayofyear •week •hour •minute •second •millisecond Start specifies the beginning date for the difference calculation. Start is an expression that returns a DATETIME value or a Unicode character string in a DATE format. End specifies the ending date for the difference calculation. End is an expression that returns a DATETIME value or a Unicode character string in a DATE format. Start is subtracted from end. An error is returned if the return value is outside of the range for integer values. See Data Type Ranges. |
DATEFROMPARTS(year, month, day) | Returns a date value for the specified year, month, and day. NULL is returned if any of the parameters are NULL. |
DATENAME(datepart, date_exp) | Returns an English character string (a VARCHAR) that represents the datepart of date_exp. For example, a datepart month returns the name of the month such as January, February, and so forth. A datepart weekday returns the day of the week such as Monday, Tuesday, and so forth. Datepart specifies the part of the date to return and must be one of the following values: •year •quarter •month •day •dayofyear •week •weekday •hour •minute •second •millisecond Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME. |
DATEPART(datepart, date_exp) | Returns an integer that represents the datepart of date_exp. For example, a datepart month returns an integer representing the month (January = 1, December = 12). A datepart weekday returns an integer representing the day of the week (Sunday = 1, Saturday = 7). Datepart is the part of the date to return and must be one of the following values: •year •quarter •month •day •dayofyear •week •weekday •hour •minute •second •millisecond •TZoffset The TZoffset value returns a time zone offset in number of minutes (signed). The DATEPART function with TZoffset works only with SYSDATETIMEOFFSET() and string literals containing a time zone offset. The time zone offset range is -14:00 through +14:00. See Time and Date Function Examples. Date_exp is an expression that returns a DATETIME value, a value that can be implicitly converted to a DATETIME value, or a character string in a DATE format. See DATETIME. |
DAY(date_exp) | |
DAYNAME(date_exp) | Returns an English character string containing the name of the day (for example, Sunday through Saturday) for the day portion of date_exp. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
DAYOFMONTH(date_exp) | Returns the day of the month in date_exp as an integer in the range of 1 to 31. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
DAYOFYEAR(date_exp) | Returns the day of the year based on the year field in date_exp as an integer value in the range of 1-366. |
DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds) | Returns a value constructed from the provided parameters. NULL is returned if any of the parameters are NULL. |
DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, scale) | Returns a string value for a date and time using specified parameters. If any of the parameters except scale are NULL, then NULL is returned. If scale is NULL, then an error is returned. Scale specifies the precision of the fractions value and has a range from 0 to 7. Fractions depends on scale and has a range from 0 to 9999999. For example, if scale is 3, then each fraction represents a millisecond. The number of digits specified for fractions must be less or equal to the value for scale. The hour_offset specifies the hour portion of a time zone with a range from -14 to +14. The minute_offset specifies the minute portion of a time zone with a range from 0 to 59. Hour_offset and minute_offset must have the same sign unless hour offset is 0. The default string literal format for DATETIMEOFFSETFROMPARTS is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. |
EXTRACT(extract_field, extract_source) | Returns the extract_field portion of the extract_source. The extract_source argument is a date, time, or interval expression. The following values are permitted for extract_field and are returned from the target expression: •YEAR •MONTH •DAY •HOUR •MINUTE •SECOND |
HOUR(time_exp) | Returns the hour as an integer in the rage of 0 to 23. Time_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
MINUTE(time_exp) | Returns the minute as an integer in the range 0 to 59. Time_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
MONTH(date_exp) | Returns the month as an integer in the range of 1 to 12. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
MONTHNAME(date_exp) | Returns an English character string containing the name of the month (for example, January through December) for the month portion of date_exp. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
NOW() | Returns the current local date and time in the format 'yyyy-mm-dd hh:mm:ss.mmm'. Uses the local clock time by default. If SET TIME ZONE has been called, then the value of NOW() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE. |
QUARTER(date_exp) | Returns the quarter in date_exp as an integer value in the range of 1- 4, where 1 represents January 1 through March 31. Date_exp can bebe a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
SECOND(time_exp) | Returns the second as an integer in the range of 0 to 59. Time_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
SYSDATETIME() | Returns the current local date and time displayed in the format 'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'. Uses the local clock time by default. Scale is septaseconds on Windows 10, nanoseconds on Linux, and microseconds on all other platforms. Trailing digits not returned are padded with zeros to 9 places. If SET TIME ZONE has been called, then the value of SYSDATETIME() is determined by calculating UTC time and date from the system clock and operating system locale setting, then adding the offset value from SET TIME ZONE. |
SYSDATETIMEOFFSET() | Returns the current date and time along with the hour and minute offset between the current time zone and UTC of the computer on which the Zen database engine is running. Daylight saving time (DST) is accounted for. The default format returned is YYYY-MM-DD hh:mm:ss[.nnnnnnnnn] [<+ | ->hh:mm]. A plus sign indicates that the current time zone is ahead of the UTC. A minus sign indicates that the current time zone is behind the UTC. |
SYSUTCDATETIME() | Returns the current local date and time displayed in the format 'yyyy-mm-dd hh:mm:ss.nnnnnnnnn'. Uses the local clock time by default. Scale is septaseconds on Windows 10, nanoseconds on Linux, and microseconds on all other platforms. Trailing digits not returned are padded with zeros to 9 places. If SET TIME ZONE has been called, then the value of SYSUTCDATETIME() is determined by calculating UTC time and date from the system clock and operating system locale setting. |
TIMEFROMPARTS(hour, minute, seconds, fractions, scale) | Returns a time value constructed from the specified time parameters. If any of the parameters except scale are NULL, then NULL is returned. If scale is NULL, then an error is returned. Scale specifies the precision of the fractions value and has a range from 0 to 7. Fractions depends on scale and has a range from 0 to 9999999. For example, if scale is 3, then each fraction represents a millisecond. The number of digits specified for fractions must be less or equal to the value for scale. The default format for TIMEFROMPARTS is hh:mm:ss[.nnnnnnn]. |
TIMESTAMPADD(interval, integer_exp, timestamp_exp) | Returns the time stamp calculated by adding integer_exp intervals of type interval to timestamp_exp. The following values are allowed for interval: •SQL_TSI_YEAR •SQL_TSI_QUARTER •SQL_TSI_MONTH •SQL_TSI_WEEK •SQL_TSI_DAY •SQL_TSI_HOUR •SQL_TSI_MINUTE •SQL_TSI_SECOND |
TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2) | Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. The values allowed for interval are the same as for TIMESTAMPADD. |
WEEK(date_exp) | Returns the week of the year based on the week field in date_exp as an integer in the range of 1 to 53. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
WEEKDAY(date_exp) | |
YEAR(date_exp) | Returns the year as an integer value. The range depends on the data source. Date_exp can be a DATE, SQL_TIMESTAMP literal, or a column containing DATE, DATETIME, or time stamp data. |
Function | Description |
---|---|
DATABASE() | Returns the current database name. |
NEWID() | Creates a unique value for data type uniqueidentifier |
USER() | Returns the login name of the current user. |
Function | Description |
---|---|
COALESCE (expression1, expression2 [, ... ] ) | Returns the first non-null argument, starting from the left in the expression list. See also COALESCE for additional details. |
IF (predicate, expression1, expression2) | Returns expression1 if predicate is true. Otherwise returns expression2. |
NULL() | Sets a column as NULL values. |
IFNULL (exp, value) | If exp is NULL, value is returned. If exp is not null, exp is returned. The possible data type or types of value must be compatible with the data type of exp. |
ISNULL (exp, value) | Replaces NULL with the value specified for value. Exp is the expression to check for NULL. Value is the value returned if exp is NULL. Exp is returned if it is not NULL. The data type of value must be compatible with the data type of exp. |
NULLIF (exp1, exp2) | NULLIF returns exp1 if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a NULL value. |
Function | Description | |
---|---|---|
CAST (exp AS type) TRY_CAST (exp AS type) | Converts exp to type, where type may be a data type listed under Zen Transactional and Relational Data Types, which includes precision and scale parameters. | |
CONVERT (exp, type [, style ]) TRY_CONVERT (exp, type [, style ]) | Converts exp to the type indicated, using the following type arguments: SQL_BIGINT SQL_BINARY SQL_BIT SQL_CHAR SQL_DATE SQL_DECIMAL SQL_DOUBLE SQL_FLOAT SQL_GUID SQL_INTEGER SQL_LONGVARBINARY SQL_LONGVARCHAR SQL_NUMERIC SQL_REAL SQL_SMALLINT SQL_TIME | SQL_TIMESTAMP SQL_TINYINT SQL_VARCHAR SQL_WCHAR SQL_WLONGVARCHAR SQL_WVARCHAR The CONVERT arguments use SQL_ as a prefix for the data type. The Zen relational data types do not include the SQL_ prefix. Precision and scale take default values. The optional parameter style applies only to the DATETIME data type. Use of the parameter truncates the milliseconds portion of the DATETIME data type. A style value may be either 20 or 120. Both values specify the canonical format: yyyy-mm-dd hh:mm:ss. See Conversion Function Examples below. |