> # Scalar Functions

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

>     {fn scalar-function}

> where `scalar-function`

> is a supported scalar function, as shown in the following tables.

> Example:

>     SELECT {fn UCASE(NAME)} FROM EMP

>     Table A-1.  Scalar Functions Supported on DB2

>     Data Store

>     String
>     Functions

>     Numeric Functions

>     Timedate Functions

>     System Functions

>     DB2 UDB on z/OS

>     CHAR
>     CONCAT
>     DIFFERENCE
>     INSERT
>     LCASE
>     LEFT
>     LENGTH
>     LOCATE
>     LOCATE_2
>     LTRIM
>     REPEAT
>     REPLACE
>     RIGHT
>     RTRIM
>     SOUNDEX
>     SPACE
>     SUBSTRING
>     UCASE

>     ABS
>     ACOS
>     ASIN
>     ATAN
>     ATAN2
>     CEILING
>     COS
>     DEGREES
>     EXP
>     FLOOR
>     LOG
>     LOG10
>     MOD
>     PI
>     POWER
>     RADIANS
>     RAND
>     ROUND
>     SIGN
>     SIN
>     SQRT
>     TAN
>     TRUNCATE

>     CURDATE
>     CURRENT_DATE
>     CURTIME
>     DAYOFMONTH
>     DAYOFWEEK
>     DAYOFYEAR
>     HOUR
>     MINUTE
>     MONTH
>     NOW
>     SECOND
>     WEEK
>     YEAR

>     DBNAME
>     IFNULL
>     USERNAME

>     DB2 UDB on Linux/UNIX/ Windows

>     ASCII
>     CHAR
>     CONCAT
>     DIFFERENCE
>     INSERT
>     LCASE
>     LEFT
>     LENGTH
>     LOCATE
>     LOCATE_2
>     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

>     CURDATE
>     CURRENT_DATE
>     CURTIME
>     DAYNAME
>     DAYOFMONTH
>     DAYOFWEEK
>     DAYOFYEAR
>     HOUR
>     MINUTE
>     MONTH
>     MONTHNAME
>     NOW
>     QUARTER
>     SECOND
>     TIMESTAMPADD
>     TIMESTAMPDIFF
>     WEEK
>     YEAR

>     DATABASE
>     IFNULL
>     USERNAME

>     Table A-2.  Scalar Functions Supported on Informix

>     Data Store

>     String
>     Functions

>     Numeric Functions

>     Timedate Functions

>     System Functions

>     Informix

>     BIT_LENGTH
>     CHAR_LENGTH
>     CONCAT
>     LENGTH
>     LTRIM
>     RTRIM
>     STR_LENGTH

>     ABS
>     ACOS
>     ASIN
>     ATAN
>     ATAN2
>     COS
>     COT
>     EXP
>     FLOOR
>     LOG
>     LOG10
>     MOD
>     POWER
>     ROUND
>     SQRT
>     TAN
>     TRUNCATE

>     CURDATE
>     CURRENT DATE
>     CURTIME
>     DAYOFMONTH
>     DAYOFWEEK
>     MONTH
>     NOW
>     QUARTER
>     YEAR

>     DBNAME
>     USERNAME

>     Table A-3.  Scalar Functions Supported on Microsoft SQL Server

>     Data Store

>     String
>     Functions

>     Numeric Functions

>     Timedate Functions

>     System Functions

>     Microsoft
>     SQL Server

>     ASCII
>     BITLENGTH
>     CHAR
>     CONCAT
>     DIFFERENCE
>     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
>     DEGREES
>     EXP
>     FLOOR
>     LOG
>     LOG10
>     MOD
>     PI
>     POWER
>     RADIANS
>     RAND
>     ROUND
>     SIGN
>     SIN
>     SQRT
>     TAN
>     TRUNCATE

>     CURDATE
>     CURRENT_DATE
>     CURRENT_TIME
>     CURRENT_ TIMESTAMP
>     CURTIME
>     DAYOFMONTH
>     DAYOFWEEK
>     DAYOFYEAR
>     DAYNAME
>     EXTRACT
>     HOUR
>     MINUTE
>     MONTH
>     MONTHNAME
>     NOW
>     QUARTER
>     SECOND
>     TIMESTAMPADD
>     TIMESTAMPDIFF
>     WEEK
>     YEAR

>     DBNAME
>     IFNULL
>     USERNAME

>     Table A-4.  Scalar Functions Supported on Oracle

>     Data Store

>     String
>     Functions

>     Numeric Functions

>     Timedate Functions

>     System Functions

>     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
>     CURRENT_DATE
>     CURRENT_TIME
>     CURRENT TIMESTAMP
>     DAYOFMONTH
>     DAYOFWEEK
>     DAYOFYEAR
>     DAYNAME
>     HOUR
>     MINUTE
>     MONTH
>     MONTHNAME
>     NOW
>     QUARTER
>     SECOND
>     TIMESTAMP_ADD
>     TIMESTAMP_DIFF
>     WEEK
>     YEAR

>     IFNULL
>     USER
>     USERNAME

>     Table A-5.  Scalar Functions Supported on SybaseScalar Functions Supported on Sybase

>     Data Store

>     String
>     Functions

>     Numeric Functions

>     Timedate Functions

>     System Functions

>     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
>     DEGREES1
>     EXP
>     FLOOR
>     LOG
>     LOG10
>     MOD
>     NUM_DEGREES
>     NUM_RADIANS
>     PI
>     POWER
>     RADIANS
>     RAND
>     ROUND
>     SIGN
>     SIN
>     SQRT
>     TAN
>     TRUNCATE

>     CURDATE
>     CURRENT_DATE
>     DAYOFMONTH
>     DAYOFWEEK
>     DAYOFYEAR
>     DAYNAME
>     HOUR
>     MINUTE
>     MONTH
>     MONTHNAME
>     NOW
>     QUARTER
>     SECOND
>     TIMESTAMPADD
>     TIMESTAMPDIFF
>     WEEK
>     YEAR

>     DATABASE
>     DBNAME
>     IFNULL
>     USER
>     USERNAME

> ### String Functions

> Table A-6 lists string functions. The following arguments can be used with these functions:

> - string_exp can be a column name, a string literal, or the result of another scalar function, where the underlying data type is SQL_CHAR or SQL_WCHAR, SQL_VARCHAR or SQL_WVARCHAR, or SQL_LONGVARCHAR or SQL_WLONGVARCHAR.
> - start, length, and count can be the result of another scalar function or a literal numeric value, where the underlying data type is SQL_TINYINT, SQL_SMALLINT, or SQL_INTEGER.

> The string functions are one-based; that is, the first character in the string is the character 1. Character string literals must be enclosed by single quotation marks.

> Table A-6. Scalar String Functions
> Function | Returns
> --- | ---
> ASCII(string_exp) | The ASCII code of the leftmost character of string_exp as an integer.
> BIT_LENGTH(string_exp) | The length, in bits, of the string expression.
> CHAR(code) | The character with the ASCII code specified by code. code should be between 0 and 255; otherwise, the return value depends on the data source.
> CHAR_LENGTH(string_exp) | The length, in characters, of the string expression, when the string expression is a character data type; otherwise, the length, in bytes, of the string expression (the lowest integer that is not less than the number of bits divided by 8). (This function is the same as the CHARACTER_LENGTH function.)
> CHARACTER_LENGTH(string_exp) | The length, in characters, of the string expression, when the string expression is a character data type; otherwise, the length, in bytes, of the string expression (the lowest integer that is not less than the number of bits divided by 8). (This function is the same as the CHAR_LENGTH function.)
> CONCAT(string_exp1, string_exp2) | The string resulting from concatenating string_exp2 and string_exp1. The string is system dependent.
> DIFFERENCE(string_exp1, string_exp2) | An integer indicating the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2.
> INSERT(string_exp1, start, length, string_exp2) | A string where length characters have been deleted from string_exp1 beginning at start and where string_exp2 has been inserted into string_exp beginning at start.
> LCASE(string_exp) | Uppercase characters in string_exp converted to lowercase.
> LEFT(string_exp,count) | The count of characters of string_exp.
> LENGTH(string_exp) | The number of characters in string_exp, excluding trailing blanks and the string termination character.
> LOCATE(string_exp1, string_exp2[,start]) | The starting position of the first occurrence of string_exp1 in string_exp2. If start is not specified, the search begins with the first character position in string_exp2. If start is specified, the search begins with the character position indicated by start. The first character position in string_exp2 is indicated by 1. If string_exp1 is not found, 0 is returned.
> LTRIM(string_exp) | The characters of string_exp, with leading blanks removed.
> OCTET_LENGTH(string_exp) | The length, in bytes, of the string expression. The result is the lowest integer that is not less than the number of bits divided by 8.
> POSITION(character_exp IN character_exp) | The position of the first character expression in the second character expression. The result is a numeric with an implementation-defined precision and a scale of 0.
> REPEAT(string_exp, count) | A string composed of string_exp repeated count times.
> REPLACE(string_exp1, string_exp2, string_exp3) | Replaces all occurrences of string_exp2 in string_exp1 with string_exp3.
> RIGHT(string_exp, count) | The rightmost count of characters in string_exp.
> RTRIM(string_exp) | The characters of string_exp with trailing blanks removed.
> SOUNDEX(string_exp) | A data-source dependent string representing the sound of the words in string_exp.
> SPACE(count) | A string consisting of count spaces.
> SUBSTRING(string_exp, start, length) | A string derived from string_exp, beginning at the character position start for length characters.
> UCASE(string_exp) | Lowercase characters in string_exp converted to uppercase.

> ### Numeric Functions

> Table A-7 lists numeric functions. The following arguments can be used with numeric functions:

> - numeric_exp can be a column name, a numeric literal, or the result of another scalar function, where the underlying data type is SQL_NUMERIC, SQL_DECIMAL, SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, SQL_BIGINT, SQL_FLOAT, SQL_REAL, or SQL_DOUBLE.
> - float_exp can be a column name, a numeric literal, or the result of another scalar function, where the underlying data type is SQL_FLOAT.
> - integer_exp can be a column name, a numeric literal, or the result of another scalar function, where the underlying data type is SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER, or SQL_BIGINT.

> Table A-7. Scalar Numeric Functions
> Function | Returns
> --- | ---
> ABS(numeric_exp) | Absolute value of numeric_exp.
> ACOS(float_exp) | Arccosine of float_exp as an angle in radians.
> ASIN(float_exp) | Arcsine of float_exp as an angle in radians.
> ATAN(float_exp) | Arctangent of float_exp as an angle in radians.
> ATAN2(float_exp1, float_exp2) | Arctangent of the x and y coordinates, specified by float_exp1 and float_exp2 as an angle in radians.
> CEILING(numeric_exp) | Smallest integer greater than or equal to numeric_exp.
> COS(float_exp) | Cosine of float_exp as an angle in radians.
> COT(float_exp) | Cotangent of float_exp as an angle in radians.
> DEGREES(numeric_exp) | Number if degrees converted from numeric_exp radians.
> EXP(float_exp) | Exponential value of float_exp.
> FLOOR(numeric_exp) | Largest integer less than or equal to numeric_exp.
> LOG(float_exp) | Natural log of float_exp.
> LOG10(float_exp) | Base 10 log of float_exp.
> MOD(integer_exp1, integer_exp2) | Remainder of integer_exp1 divided by integer_exp2.
> PI() | Constant value of pi as a floating-point number.
> POWER(numeric_exp, integer_exp) | Value of numeric_exp to the power of integer_exp.
> RADIANS(numeric_exp) | Number of radians converted from numeric_exp degrees.
> RAND([integer_exp]) | Random floating-point value using integer_exp as the optional seed value.
> ROUND(numeric_exp, integer_exp) | numeric_exp rounded to integer_exp places right of the decimal (left of the decimal if integer_exp is negative).
> SIGN(numeric_exp) | Indicator of the sign of numeric_exp. If numeric_exp < `0`, `-1` is returned. If numeric_exp = `0`, `0` is returned. If numeric_exp > `0`,`1` is returned.
> SIN(float_exp) | Sine of float_exp, where float_exp is an angle in radians.
> SQRT(float_exp) | Square root of float_exp.
> TAN(float_exp) | Tangent of float_exp, where float_exp is an angle in radians.
> TRUNCATE(numeric_exp, integer_exp) | numeric_exp truncated to integer_exp places right of the decimal. (If integer_exp is negative, truncation is to the left of the decimal.)

> ### Date and Time Functions

> Table A-8 lists date and time functions. The following arguments can be used with the date and time functions:

> - date_exp can be a column name, a date or timestamp literal, or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_DATE, or SQL_TIMESTAMP.
> - time_exp can be a column name, a timestamp or timestamp literal, or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_TIME, or SQL_TIMESTAMP.
> - timestamp_exp can be a column name; a time, date, or timestamp literal; or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_TIME, SQL_DATE, or SQL_TIMESTAMP.

> Table A-8. Scalar Time and Date Functions
> Function | Returns
> --- | ---
> CURDATE() | Current date as a date value.
> CURRENT_DATE() | Current date.
> CURRENT_TIME[(time-precision)] | Current local time. The time-precision argument determines the seconds precision of the returned value.
> CURRENT_TIMESTAMP[(timestamp- precision)] | Current local date and local time as a timestamp value. The timestamp-precision argument determines the seconds precision of the returned timestamp.
> CURTIME() | Current local time as a time value.
> DAYNAME(date_exp) | Character string containing a data-source-specific name of the day for the day portion of date_exp.
> DAYOFMONTH(date_exp) | Day of the month in date_exp as an integer value (1-31).
> DAYOFWEEK(date_exp) | Day of the week in date_exp as an integer value (1-7).
> DAYOFYEAR(date_exp) | Day of the year in date_exp as an integer value (1-366).
> EXTRACT(extract-field FROM extract-source) | Extract-field portion of the extract-source. The extract-source argument is a datetime or interval expression. The extract-field argument can be one of the following keywords: YEAR MONTH DAY HOUR MINUTE SECOND The precision scale of the returned value 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 in time_exp as an integer value (0-23).
> MINUTE(time_exp) | Minute in time_exp as an integer value (0-59).
> MONTH(date_exp) | Month in date_exp as an integer value (1-12).
> MONTHNAME(date_exp) | Character string containing the data source-specific name of the month.
> NOW() | Current date and time as a timestamp value.
> QUARTER(date_exp) | Quarter in date_exp as an integer value (1-4).
> SECOND(time_exp) | Second in time_exp as an integer value (0-59).
> TIMESTAMPADD(interval, integer_exp, time_exp) | Timestamp calculated by adding integer_exp intervals of type interval to time_exp. interval can be one of the following values: SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_WEEK SQL_TSI_MONTH SQL_TSI_QUARTER SQL_TSI_YEAR Fractional seconds are expressed in billionths of a second.
> TIMESTAMPDIFF(interval, time_exp1, time_exp2) | Integer number of intervals of type interval by which time_exp2 is greater than time_exp1. interval has the same value as TIMESTAMPADD. Fractional seconds are expressed in billionths of a second.
> WEEK(date_exp) | Week of the year in date_exp as an integer value (1-53).
> YEAR(date_exp) | Year in date_exp. The range is data-source dependent.

> ### System Functions

> Table A-9 lists system functions.

> Table A-9. Scalar System Functions
> Function | Returns
> DATABASE() | Name of the database, corresponding to the connection handle (hdbc).
> IFNULL(exp,value) | value, if exp is null.
> USER() | Authorization name of the user.