skip to main content
SQL Escape Sequences for JDBC : Scalar Functions
  
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 drivers, as listed in the following table.
Example:
SELECT id, name FROM emp WHERE name LIKE {fn UCASE('Smith')}
Table 34. Supported Scalar Functions
Data Store
String Functions
Numeric Functions
Timedate Functions
System Functions
DB2
ASCII
BLOB
CHAR
CHR
CLOB
CONCAT
DAYNAMEDBCLOB
DECFLOAT_FORMAT
DIFFERENCE
GRAPHIC
HEX
INITCAPINSERT
INSTR
LCASE or LOWER
LCASE 1
LEFT
LENGTH
LOCATE
LOCATE_IN_STRING
LONG_VARCHAR
LONG_VARGRAPHIC
LPAD
LTRIM
LTRIM
MONTHNAME
POSSTR
REPEAT
REPLACE
RIGHT
RPADRTRIM
RTRIM
RTRIM
SOUNDEX
SPACE
SUBSTR
TO_CLOB
TO_NUMBER
TRUNCATE or TRUNC
UCASE or UPPER
VARCHAR
VARGRAPHIC
ABS or ABSVAL
ACOS
ASIN
ATAN
ATANH
ATAN2
BIGINT
CEILING or CEIL
COS
COSH
COT
DECIMAL
DEGREES
DIGITS
DOUBLE
EXP
FLOAT
FLOOR
INTEGER
LN
LOG
LOG10
MOD
POWER
RADIANS
RAND
REAL
ROUND
SIGN
SIN
SINH
SMALLINT
SQRT
TAN
TANH
TRUNCATE
CURDATE
CURTIME
DATE
DAY
DAYNAME
DAYOFWEEK
DAYOFYEAR
DAYS
HOUR
JULIAN_DAY
MICROSECOND
MIDNIGHT_SECONDS
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIME
TIMESTAMP
TIMESTAMP_ISO
TIMESTAMPDIFF
WEEK
YEAR
COALESCE
DEREF
DLCOMMENT
DLLINKTYPE
DLURLCOMPLETE
DLURLPATH
DLURLPATHONLY
DLURLSCHEME
DLURLSERVER
DLVALUE
EVENT_MON_STATE
GENERATE_UNIQUE
NODENUMBER
NULLIF
PARTITION
RAISE_ERROR
TABLE_NAME
TABLE_SCHEMA
TRANSLATE
TYPE_ID
TYPE_NAME
TYPE_SCHEMA
VALUE
Informix
CONCAT
LEFT
LENGTH
LTRIM
REPLACE
RTRIM
SUBSTRING
ABS
ACOS
ASIN
ATAN
ATAN2
COS
COT
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
ROUND
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURTIME
DAYOFMONTH
DAYOFWEEK
MONTH
NOW
TIMESTAMPADD
TIMESTAMPDIFF
YEAR
DATABASE
USER
MySQL
ASCII
CHAR
CONCAT
INSERT
LCASE
LEFT
LENGTH
LOCATE
LOCATE_2
LTRIM
REPEAT
REPLACE
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTRING
UCASE
ABSA
COS
ASIN
ATAN
ATAN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
IFNULL
USER
Oracle
ASCII
BIT_LENGTH
CHAR
CONCAT
INSERT
LCASE
LEFT
LENGTH
LOCATE
LOCATE2
LTRIM
OCTET_LENGTH
REPEAT
REPLACE
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
WEEK
YEAR
IFNULL
USER
PostgreSQL
ASCII
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
LCASE
LENGTH
LEFT 2
LOCATE
LTRIM
OCTET_LENGTH
POSITION
REPEAT
REPLACE
RIGHT
RTRIM
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
EXTRACT
NOW
USERNAME
DBNAME
IFNULL
Progress OpenEdge
ASCII
CHAR
CONCAT
DIFFERENCE
LCASE
LEFT
LENGTH
LOCATE
LTRIM
REPEAT
REPLACE
RIGHT
RTRIM
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
DEGREES
EXP
FLOOR
LOG10
MOD
PI
POWER
RADIANS
ROUND
SIN
SQRT
TAN
CURDATE
CURTIME
DAYNAME
DAYOFMONTH
DAYOFWEEK
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
IFNULL
USER
SQL Server
ASCII
CHAR
CONCAT
DIFFERENCE
INSERT
LCASE
LEFT
LENGTH
LOCATE
LTRIM
REPEAT
REPLACE
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
IFNULL
USER
Sybase
ASCII
CHAR
CONCAT
DIFFERENCE
INSERT
LCASE
LEFT
LENGTH
LOCATE
LTRIM
REPEAT
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TIMESTAMPADD
TIMESTAMPDIFF
WEEK
YEAR
DATABASE
IFNULL
USER
Apache Hive
ASCII
CONCAT
INSERT
LCASE
LEFT
LENGTH
LOCATE
LOCATE2
LTRIM
REPEAT
REPLACE
RIGHT
RTRIM
SPACE
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MODP
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DAYOFMONTH
EXTRACT
HOUR
MINUTE
MONTH
NOW
QUARTER
SECOND
TIMESTAMPADD 3
TIMESTAMPDIFF
WEEK
YEAR
DBNAME
IFNULL
Greenplum
ASCII
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
LCASE
LENGTH
LOCATE
LTRIM
OCTET_LENGTH
POSITION
REPEAT
REPLACE
RIGHT
RTRIM
SUBSTRING
UCASE
ABS
ACOS
ASIN
ATAN
ATAN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
EXTRACT
NOW
USERNAME
DBNAME
IFNULL
Salesforce
ASCII
BITLENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
DIFFERENCE
HEXTORAW
INSERT
LCASE
LEFT
LENGTH
LOCATE
LOWER
LTRIM
OCTET_LENGTH
RAWTOHEX
REPEAT
REPLACE
RIGHT
RTRIM
SOUNDEX
SPACE
SUBSTR
SUBSTRING
UCASE
UPPER
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
BITOR
BITXOR
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MOD
PI
POWER
RADIANS
RAND
ROUND
ROUNDMAGIC
SIGN
SIN
SQRT
TAN
TRUNCATE
CURDATE
CURTIME
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
HOUR
MINUTE
MONTH
MONTHNAME
NOW
QUARTER
SECOND
TO_CHAR
WEEK
YEAR
CURSESSIONID
DATABASE
IDENTITY
USER

1 SYSFUN schema.

2 Supported for PostgreSQL 9.1 and higher

3 Apache Hive is limited to adding only days to a timestamp.