Supported scalar functions

You can use scalar functions in SQL statements with the following syntax:

{fn scalar-function}

where:

scalar-function

is a scalar function supported by the driver, as listed in the following table.

Example:

SELECT id, name FROM emp WHERE name LIKE {fn UCASE('Smith')}

Refer to Scalar functions in the Progress DataDirect for JDBC Drivers Reference for more information.

Table 1. Supported Scalar Functions for Db2 for Linux, UNIX, and Windows

String Functions	Numeric Functions	Timedate Functions	System Functions
ASCII BLOB CHAR CHAR_LENGTH CHR CLOB CONCAT DBCLOB DIFFERENCE GRAPHIC HEX INSERT LCASE or LOWER LCASE 1 LEFT LENGTH LOCATE LONG_VARCHAR LONG_VARGRAPHIC LTRIM OCTET_LENGTH POSSTR REPEAT REPLACE RIGHT RTRIM SOUNDEX SPACE SUBSTR SUBSTRING TRUNCATE or TRUNC UCASE or UPPER VARCHAR VARGRAPHIC	ABS or ABSVAL ACOS ASIN ATAN ATAN2 BIGINT CEILING or CEIL COS COT DECIMAL DEGREES DIGITS DOUBLE EXP FLOAT FLOOR INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL ROUND SIGN SIN SMALLINT SQRT TAN TRUNCATE ATANH COSH SINH TANH	CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP DATE DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR DAYS CURDATE CURTIME EXTRACT HOUR JULIAN_DAY MICROSECOND MIDNIGHT_SECONDS MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIME TIMESTAMP TIMESTAMPADD TIMESTAMP_ISO TIMESTAMPDIFF WEEK YEAR	COALESCE DEREF DLCOMMENT DLLINKTYPE DLURLCOMPLETE DLURLPATH DLURLPATHONLY DLURLSCHEME DLURLSERVER DLVALUE EVENT_MON_STATE GENERATE_UNIQUE IFNULL NODENUMBER NULLIF PARTITION RAISE_ERROR TABLE_NAME TABLE_SCHEMA TRANSLATE TYPE_ID TYPE_NAME TYPE_SCHEMA VALUE

Table 2. Supported Scalar Functions for Db2 for z/OS

String Functions	Numeric Functions	Timedate Functions	System Functions
BLOB CHAR CLOB CONCAT DBCLOB GRAPHIC HEX INSERT LCASE or LOWER LCASE 2 LEFT LENGTH LOCATE LTRIM POSSTR REPEAT REPLACE RIGHT RTRIM SPACE SUBSTR STRIP TRUNCATE or TRUNC UCASE or UPPER VARCHAR VARGRAPHIC	ABS or ABSVAL ACOS ASIN ATAN ATANH ATAN2 BIGINT CEILING or CEIL COS COSH CURRENCY DEC DECIMAL DEGREES DIGITS DOUBLE DOUBLE_PRECISION EXP FLOAT FLOOR INT INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL ROUND ROWID SIGN SIN SINH SMALLINT SQRT TAN TANH TRUNCATE	ALTDATE ALTTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DAY DAYOFMONTH DAYOFWEEK DAYOFYEAR DAYS HOUR JULIAN_DAY MICROSECOND MIDNIGHT_SECONDS MINUTE MONTH MONTHNAME NOW QUARTER SECOND TIME TIMESTAMP TIMESTAMPADD WEATHER WEEK YEAR	COALESCE IFNULL NULLIF RAISE_ERROR TABLE_NAME TABLE_LOCATION TABLE_SCHEMA TRANSLATE VALUE

Table 3. Supported Scalar Functions for Db2 for i

String Functions	Numeric Functions	Timedate Functions	System Functions
BLOB CHAR CLOB CONCAT DBCLOB GRAPHIC HEX INSERT LCASE or LOWER LCASE 3 LEFT LENGTH LOCATE LTRIM POSSTR RIGHT RTRIM SPACE SUBSTR STRIP TRUNCATE or TRUNC UCASE or UPPER VARCHAR VARGRAPHIC	ABS or ABSVAL ACOS ASIN ATAN ATANH ATAN2 BIGINT CEILING or CEIL COS COT COSH CURRENCY DEC DECIMAL DEGREES DIGITS DOUBLE DOUBLE_PRECISION EXP FLOAT FLOOR INT INTEGER LN LOG LOG10 MOD POWER RADIANS RAND REAL ROUND ROWID SIGN SIN SINH SMALLINT SQRT TAN TANH TRUNCATE	ALTDATE ALTTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DAY DAYOFMONTH DAYOFWEEK DAYOFYEAR DAYS HOUR JULIAN_DAY MICROSECOND MIDNIGHT_SECONDS MINUTE MONTH NOW QUARTER SECOND TIME TIMESTAMP TIMESTAMPADD WEATHER WEEK YEAR	COALESCE IFNULL NULLIF RAISE_ERROR TABLE_NAME TABLE_LOCATION TABLE_SCHEMA TRANSLATE VALUE