Using the PxPlus SQL ODBC Driver

Function

Description

ASCII(string)

Integer representing the ASCII code value of the leftmost character of string.

BIT_LENGTH(string)

Length in bits of string.

CHAR(num)

Character that has the ASCII code value specified by num. The value of num should be between 0 and 255.

CHAR_LENGTH(string) or

CHARACTER_LENGTH(string)

Length in characters of string if string is of a character data type.

CONCAT(string1, string2)

Character string that is the result of concatenating string2 to string1.

DIFFERENCE(string1, string2)

Integer value that indicates the difference between the values returned by the SOUNDEX function for string1 and string2.

INSERT(string1, start, length, string2)

Character string where length characters have been deleted from string1, beginning at start, and where string2 has been inserted into string1, beginning at start.

LCASE(string)

String equal to that in string, with all uppercase characters converted to lowercase.

LEFT(string, count)

Leftmost count characters of string.

LENGTH(string) or

LEN(string)

Number of characters in string, excluding trailing blanks.

LOCATE(string1, string2[, start])

Starting position of the first occurrence of string1 within string2.

The search for the first occurrence of string1 begins with the first character position in string2 unless the optional argument start is specified.

If start is specified, the search begins with the character position indicated by the value of start. The first character position in string2 is indicated by the value 1.

If string1 is not found within string2, the value 0 is returned.

LTRIM(string)

Characters of string with leading blanks removed.

OCTET_LENGTH(string)

Returns the length in bytes of string.

POSITION(string1 IN string2)

Position of string1 in string2. The result is an exact numeric with precision of double and a scale of 0.

REPEAT(string, count)

Character string composed of string repeated count times.

REPLACE(string1, string2, string3)

Search string1 for occurrences of string2 and replace with string3.

RIGHT(string, count)

Rightmost count characters of string.

RTRIM(string)

Characters of string with trailing blanks removed.

SOUNDEX(string)

4-digit SOUNDEX code.

SPACE(count)

Character string consisting of count spaces.

SUBSTRING(string, start, length) or

SUBSTR(string, start, length)

Character string that is derived from string, beginning at the character position specified by start for length characters.

UCASE(string)

String equal to that in string, but with all lowercase characters converted to uppercase.

Function

Description

ABS(num)

Absolute value of num.

ACOS(float)

Arc-cosine of float as an angle expressed in radians.

ASIN(float)

Arc-sine of float as an angle expressed in radians.

ATAN(float)

Arc-tangent of float as an angle expressed in radians.

ATAN2(float1, float2)

Arc-tangent of the x and y coordinates, specified by float1 and float2 respectively as an angle expressed in radians.

CEILING(num)

Smallest integer greater than or equal to num. The return value is of the same data type as the input parameter.

COS(float)

Cosine of float where float is an angle expressed in radians.

COT(float)

Cotangent of float where float is an angle expressed in radians.

DEGREES(num)

Number of degrees converted from num radians.

EXP(float)

Exponential value of float.

FLOOR(num)

Largest integer less than or equal to num. The return value is of the same data type as the input parameter.

LOG(float)

Natural logarithm of float.

LOG10(float)

Base 10 logarithm of float.

MOD(int1, int2)

Remainder (modulus) of int1 divided by int2.

NUM(string) or

NUMBER(string)

Converts string into a number.

PI( )

Constant value of "pi" as a floating-point value. Pi is defined internally as 3.14159265358979323846264338327950288419716939937510.

POWER(num, int)

Value of num to the power of int.

RADIANS(num)

Number of radians converted from num degrees.

RAND([int])

Random floating-point value using int as the optional seed value.

ROUND(num, int)

Returns num rounded to int places right of the decimal point. If int is negative, num is rounded int places to the left of the decimal point.

SIGN(num)

Returns an indicator of the sign of num.

Where:

   If num is less than zero, -1 is returned.
   If num equals zero, 0 is returned.
   If num is greater than zero, 1 is returned.

SIN(float)

Sine of float where float is an angle expressed in radians.

SQRT(float)

Square root of float.

TAN(float)

Tangent of float where float is an angle expressed in radians.

TRUNCATE(num, int)

Returns num truncated to int places right of the decimal point. If int is negative, num is truncated int places to the left of the decimal point.

Function

Description

CURRENT_DATE( ) or

CURDATE( )

Current date.

CURRENT_TIME( ) or 

CURTIME( )

Current local time.

CURRENT_TIMESTAMP( )

Current local date and local time as a timestamp value.

DAYNAME(date_exp)

Character string containing the name of the day for the day portion of date_exp. Only long English names are returned (e.g. Monday through Sunday).

DAYOFMONTH(date_exp)

Day of the month based on the month field in date_exp as an integer value in the range of 1 - 31.

DAYOFWEEK(date_exp)

Day of the week based on the week field in date_exp as an integer value in the range of 1 - 7, where 1 represents Sunday.

DAYOFYEAR(date_exp)

Day of the year based on the year field in date_exp as an integer value in the range of 1 - 366.

EXTRACT(extract-field FROM extract-source)

Returns the extract-field portion of the extract-source.

Where:

The precision of the returned value is implementation defined. The scale is 0 unless SECOND is specified, in which case, the scale is not less than the fractional seconds precision of the extract-source field.

HOUR(time_exp)

Hour based on the hour field in time_exp as an integer value in the range of 0 - 23.

MINUTE(time_exp)

Minute based on the minute field in time_exp as an integer value in the range of 0 - 59.

MONTH(date_exp)

Month based on the month field in date_exp as an integer value in the range of 1 - 12.

MONTHNAME(date_exp)

Character string containing the name of the month for the month portion of date_exp. Only long English names are returned (e.g. January through December).

NOW( )

Current date and time as a timestamp value.

QUARTER(date_exp)

Quarter in date_exp as an integer value in the range of 1 - 4, where 1 represents January 1 through March 31.

SECOND(time_exp)

Second based on the second field in time_exp as an integer value in the range of 0 - 59.

TIMESTAMPADD(interval, integer_exp, timestamp_exp)

Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Mmmmm

Valid values of interval include the following keywords:

Example:

The following SQL statement returns the name of each employee and his/her one year anniversary date:

   SELECT NAME, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HIRE_DATE)} FROM EMPLOYEES

If timestamp_exp is a time value and interval specifies days, weeks, months, quarters or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp.

If timestamp_exp is a date value and interval specifies seconds, minutes or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp.

TIMESTAMPDIFF(interval, timestamp_exp1, timestamp_exp2)

Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Mmmm

Valid values of interval include the following keywords:

Example:

The following SQL statement returns the name of each employee and the number of years he/she has been employed:

   SELECT NAME, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE( )}, HIRE_DATE)} FROM EMPLOYEES

If either timestamp expression is a time value and interval specifies days, weeks, months, quarters or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps.

If either timestamp expression is a date value and interval specifies seconds, minutes or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps.

WEEK(date_exp)

Week of the year based on the week field in date_exp as an integer value in the range of 1-53.

YEAR(date_exp)

Year based on the year field in date_exp as an integer value.

Function

Description

CASE expression
WHEN expression THEN expression [.n]
[ELSE expression] 
END

Simple Case function. Evaluate a list of WHEN expressions for equality with the CASE expression and return the related result expression.

If no WHEN expression is equal to the CASE expression, return the ELSE expression if present. If there is no ELSE expression, then return NULL.

CASE
WHEN boolean THEN expression [.n]
[ELSE expression] 
END

Searched Case function. Evaluate a list of Booleans and return the related result expression of the first Boolean in the list that is true.

If no WHEN expression is equal to the CASE expression, return the ELSE expression if present. If there is no ELSE expression, then return NULL.

CAST(expression AS data_type [( integer1 [, integer2] )] )

Convert expression into data_type. Optionally specify the length or precision or precision and scale of the converted to data_type. (Added in PxPlus 2016)

For details on supported data types, see Data Types.

CONVERT(expression, data_type)

Convert expression into data_type.

For details on supported data types, see Data Types.

DATABASE( )

Return the current Data Source Name (DSN) description text.

IFNULL(expression1, expression2)

If expression1 evaluates to NULL, then the result of expression2 is returned; otherwise, the result of expression1 is returned.

Data Type

Description

SQL_BIGINT

BIGINT

Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0.

SQL_BINARY [(length)]

BINARY [(length)]

Binary data of fixed length. The length can be optionally overridden using the CAST function. Binary data is returned as two characters per byte Hex representation.

Example:

   "hello world" is "68656C6C6F20776F726C64".

SQL_VARBINARY [(length)]

VARBINARY [(length)]

Variable length binary data. The maximum length can be optionally overridden using the CAST function. Binary data is returned as two characters per byte Hex representation.

Example:

   "hello world" is "68656C6C6F20776F726C64".

SQL_BIT

BIT

Single bit binary data; i.e. 0 or 1.

SQL_CHAR [(length)]

CHAR [(length)]

CHARACTER [(length)]

Character string of fixed length. The length can be optionally overridden using the CAST function.

SQL_VARCHAR [(length)]

VARCHAR [(length)]

CHAR VARYING [(length)]

CHARACTER VARYING [(length)]

Variable length character string. The maximum length can be optionally overridden using the CAST function.

SQL_DATE

SQL_TYPE_DATE

DATE

A calendar date in the format YYYYMMDD.

SQL_DECIMAL [(precision[, scale])]

DECIMAL [(precision[, scale])]

DEC [(precision[, scale])]

Signed, exact, numeric value with a precision of 15 and scale of 3. The precision and/or scale can be optionally overridden using the CAST function.

SQL_DOUBLE

DOUBLE

DOUBLE PRECISION

Signed, approximate, numeric value with a precision of 15.

SQL_FLOAT [(precision)]

FLOAT [(precision)]

Signed, approximate, numeric value with precision of 15. The precision can be optionally overridden using the CAST function.

SQL_INTEGER

INTEGER

INT

Exact numeric value with precision 10 and scale 0.

SQL_NUMERIC [(precision[, scale])]

NUMERIC [(precision[, scale])]

Signed, exact, numeric value with a precision of 15 and scale of 3. The precision and/or scale can be optionally overridden using the CAST function.

SQL_REAL

REAL

Signed, approximate, numeric value with a precision of 7.

SQL_SMALLINT

SMALLINT

Exact numeric value with precision of 5 and scale of 0.

SQL_TINYINT

TINYINT

Exact numeric value with precision of 3 and scale of 0.