Extended scalar functions for CLI applications

You use scalar functions when you want a measure of a value or if you want a value that is dependant on a numerical value, such as location in a string.

You can use escape clauses in ODBC by calling a function with the escape clause syntax or by calling the equivalent Db2® function. The following functions are defined by ODBC using vendor escape clauses. Each function can be called using the escape clause syntax, or calling the equivalent Db2 function.

These functions are presented in the following categories:

The tables in the following sections indicates for which servers (and the earliest versions) that the function can be accessed, when called from an application using CLI.

All errors detected by the following functions, when connected to a Db2 Version 5 or later server, will return SQLSTATE 38552. The text portion of the message is of the form SYSFUN:nn where nn is one of the following reason codes:

01
Numeric value out of range
02
Division by zero
03
Arithmetic overflow or underflow
04
Invalid date format
05
Invalid time format
06
Invalid timestamp format
07
Invalid character representation of a timestamp duration
08
Invalid interval type (must be one of 1, 2, 4, 8, 16, 32, 64, 128, 256)
09
String too long
10
Length or position in string function out of range
11
Invalid character representation of a floating point number

String functions

The string functions in this section are supported by CLI and defined by ODBC using vendor escape clauses.

  • Character string literals used as arguments to scalar functions must be bounded by single quotation marks.
  • Arguments denoted as string_exp can be the name of a column, a string literal, or the result of another scalar function, where the underlying data type can be represented as SQL_CHAR, SQL_VARCHAR, SQL_LONGVARCHAR, or SQL_CLOB.
  • Arguments denoted as start, length, code or count can be a numeric literal or the result of another scalar function, where the underlying data type is integer based (SQL_SMALLINT, SQL_INTEGER).
  • The first character in the string is considered to be at position 1.
Table 1. String scalar functions
String scalar function Description Servers that support the function
ASCII( string_exp ) Returns the ASCII code value of the leftmost character of string_exp as an integer. Db2
CHAR( code ) Returns the character that has the ASCII code value specified by code. The value of code should be between 0 and 255; otherwise, the return value is null. Db2
CONCAT( string_exp1, string_exp2 ) Returns a character string that is the result of concatenating string_exp2 to string_exp1. Db2, Db2 for z/OS®, Db2 Server for VM and VSE, IBM® Db2 for IBM i
DIFFERENCE( string_exp1, string_exp2 ) Returns an integer value indicating the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2. Db2, IBM Db2 for IBM i
INSERT( string_exp1, start, length, string_exp2 ) Returns a character string where length number of characters beginning at start has been replaced by string_exp2 which contains length characters. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
LCASE( string_exp ) Converts all uppercase characters in string_exp to lowercase. Db2, Db2 for z/OS, Db2 Server for VM and VSE
LEFT( string_exp,count ) Returns the leftmost count of characters of string_exp. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
LENGTH( string_exp ) Returns the number of characters in string_exp, excluding trailing blanks and the string termination character.
Note: Trailing blanks are included for Db2 Server for VM and VSE.
 Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
LOCATE( string_exp1, string_exp2 [ ,start ]) Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with first character position in string_exp2 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 string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned. Db2, Db2 for z/OS, IBM Db2 for IBM i
LTRIM( string_exp ) Returns the characters of string_exp with the leading blanks removed. Db2, Db2 for z/OS, IBM Db2 for IBM i
REPEAT( string_exp, count ) Returns a character string composed of string_exp repeated count times. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
REPLACE( string_exp1, string_exp2, string_exp3 ) Replaces all occurrences of string_exp2 in string_exp1 with string_exp3. Db2, Db2 for z/OS,
RIGHT( string_exp, count ) Returns the rightmost count of characters of string_exp. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
RTRIM( string_exp ) Returns the characters of string_exp with trailing blanks removed. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
SOUNDEX( string_exp1 ) Returns a four character code representing the sound of string_exp1. Note that different data sources use different algorithms to represent the sound of string_exp1. Db2, IBM Db2 for IBM i
SPACE( count ) Returns a character string consisting of count spaces. Db2, Db2 for z/OS, IBM Db2 for IBM i
SUBSTRING( string_exp, start, length ) Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
UCASE( string_exp ) Converts all lowercase characters in string_exp to uppercase. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i

Numeric functions

The numeric functions in this section are supported by CLI and defined by ODBC using vendor escape clauses.

  • Arguments denoted as numeric_exp can be the name of a column, the result of another scalar function, or a numeric literal, where the underlying data type can be either floating point based ( SQL_NUMERIC, SQL_DECIMAL, SQL_FLOAT, SQL_REAL, SQL_DOUBLE) or integer based (SQL_SMALLINT, SQL_INTEGER).
  • Arguments denoted as double_exp can be the name of a column, the result of another scalar functions, or a numeric literal where the underlying data type is floating point based.
  • Arguments denoted as integer_exp can be the name of a column, the result of another scalar functions, or a numeric literal, where the underlying data type is integer based.
Table 2. Numeric scalar functions
Numeric scalar function Description Servers that support the function
ABS( numeric_exp ) Returns the absolute value of numeric_exp Db2, Db2 for z/OS, IBM Db2 for IBM i
ACOS( double_exp ) Returns the arccosine of double_exp as an angle, expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
ASIN( double_exp ) Returns the arcsine of double_exp as an angle, expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
ATAN( double_exp ) Returns the arctangent of double_exp as an angle, expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
ATAN2( double_exp1, double_exp2 ) Returns the arctangent of x and y coordinates specified by double_exp1 and double_exp2, as an angle expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
CEILING( numeric_exp ) Returns the smallest integer greater than or equal to numeric_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
COS( double_exp ) Returns the cosine of double_exp, where double_exp is an angle expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
COT( double_exp ) Returns the cotangent of double_exp, where double_exp is an angle expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
DEGREES( numeric_exp ) Returns the number of degrees converted from numeric_exp radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
EXP( double_exp ) Returns the exponential value of double_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
FLOOR( numeric_exp ) Returns the largest integer less than or equal to numeric_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
LOG( double_exp ) Returns the natural logarithm of double_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
LOG10( double_exp ) Returns the base 10 logarithm of double_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
MOD( integer_exp1, integer_exp2 ) Returns the remainder (modulus) of integer_exp1 divided by integer_exp2. Db2, Db2 for z/OS, IBM Db2 for IBM i
PI() Returns the constant value of pi as a floating point value. Db2, Db2 for z/OS, IBM Db2 for IBM i
POWER( numeric_exp, integer_exp ) Returns the value of numeric_exp to the power of integer_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
RADIANS( numeric_exp ) Returns the number of radians converted from numeric_exp degrees. Db2, Db2 for z/OS, IBM Db2 for IBM i
RAND( [integer_exp ] ) Returns a random floating point value using integer_exp as the optional seed value. Db2, Db2 for z/OS, IBM Db2 for IBM i
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 | places to the left of the decimal point. Db2, Db2 for z/OS, IBM Db2 for IBM i
SIGN( numeric_exp ) Returns an indicator or 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. Db2, Db2 for z/OS, IBM Db2 for IBM i
SIN( double_exp ) Returns the sine of double_exp, where double_exp is an angle expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
SQRT( double_exp ) Returns the square root of double_exp. Db2, Db2 for z/OS, IBM Db2 for IBM i
TAN( double_exp ) Returns the tangent of double_exp, where double_exp is an angle expressed in radians. Db2, Db2 for z/OS, IBM Db2 for IBM i
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 | places to the left of the decimal point. Db2, Db2 for z/OS, IBM Db2 for IBM i

Date and time functions

The date and time functions in this section are supported by CLI and defined by ODBC using vendor escape clauses.

  • Arguments denoted as timestamp_exp can be the name of a column, the result of another scalar function, or a time, date, or timestamp literal.
  • Arguments denoted as date_exp can be the name of a column, the result of another scalar function, or a date or timestamp literal, where the underlying data type can be character based, or date or timestamp based.
  • Arguments denoted as time_exp can be the name of a column, the result of another scalar function, or a time or timestamp literal, where the underlying data types can be character based, or time or timestamp based.
Table 3. Date and time scalar functions
Date and time scalar function Description Servers that support the function
CURDATE() Returns the current date as a date value. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
CURTIME() Returns the current local time as a time value. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
DAYNAME( date_exp ) Returns a character string containing the name of the day (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday ) for the day portion of date_exp. Db2
DAYOFMONTH ( date_exp ) Returns the day of the month in date_exp as an integer value in the range of 1-31. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
DAYOFWEEK( date_exp ) Returns the day of the week in date_exp as an integer value in the range 1-7, where 1 represents Sunday. Db2, IBM Db2 for IBM i
DAYOFWEEK_ISO( date_exp ) Returns the day of the week in date_exp as an integer value in the range 1-7, where 1 represents Monday. Note the difference between this function and the DAYOFWEEK() function, where 1 represents Sunday. Db2, Db2 for z/OS, IBM Db2 for IBM i
DAYOFYEAR( date_exp ) Returns the day of the year in date_exp as an integer value in the range 1-366. Db2, IBM Db2 for IBM i
HOUR( time_exp ) Returns the hour in time_exp as an integer value in the range of 0-23. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
JULIAN_DAY( date_exp ) Returns the number of days between date_exp and January 1, 4712 B.C. (the start of the Julian date calendar). Db2, IBM Db2 for IBM i
MINUTE( time_exp ) Returns the minute in time_exp as integer value in the range of 0-59. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
MONTH( date_exp ) Returns the month in date_exp as an integer value in the range of 1-12. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
MONTHNAME( date_exp ) Returns a character string containing the name of month (January, February, March, April, May, June, July, August, September, October, November, December) for the month portion of date_exp. Db2
NOW() Returns the current date and time as a timestamp value. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
QUARTER( date_exp ) Returns the quarter in date_exp as an integer value in the range of 1-4. Db2, IBM Db2 for IBM i
SECOND( time_exp ) Returns the second in time_exp as an integer value in the range of 0-59. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
SECONDS_SINCE_MIDNIGHT( time_exp ) Returns the number of seconds in time_exp relative to midnight as an integer value in the range of 0-86400. If time_exp includes a fractional seconds component, the fractional seconds component will be discarded. Db2
TIMESTAMPADD( interval, integer_exp, timestamp_exp ) Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp. Valid values of interval are:
  • 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
where fractional seconds are expressed in 1/1000000000 second. If timestamp_exp specifies 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 fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 00:00:00.000000 before calculating the resulting timestamp. An application determines which intervals are supported by calling SQLGetInfo() with the SQL_TIMEDATE_ADD_INTERVALS option.		 Db2
TIMESTAMPDIFF( interval, timestamp_exp1, timestamp_exp2 ) Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1. Valid values of interval are:
  • 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
where fractional seconds are expressed in 1/1000000000 second. 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 fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps. An application determines which intervals are supported by calling SQLGetInfo() with the SQL_TIMEDATE_DIFF_INTERVALS option.		 Db2
WEEK( date_exp ) Returns the week of the year in date_exp as an integer value in the range of 1-54. Db2, IBM Db2 for IBM i
WEEK_ISO( date_exp ) Returns the week of the year in date_exp as an integer value in the range of 1-53. Week 1 is defined as the first week of the year to contain a Thursday. Therefore, Week1 is equivalent to the first week that contains Jan 4, since Monday is considered to be the first day of the week.

Note that WEEK_ISO() differs from the current definition of WEEK(), which returns a value up to 54. For the WEEK() function, Week 1 is the week containing the first Saturday. This is equivalent to the week containing Jan. 1, even if the week contains only one day.

 Db2
YEAR( date_exp ) Returns the year in date_exp as an integer value in the range of 1-9999. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i

For those functions that return a character string containing the name of the day of week or the name of the month, these character strings will be National Language Support enabled.

DAYOFWEEK_ISO() and WEEK_ISO() are automatically available in a database created in Db2 Version 7 or later. If a database was created before Version 7, these functions might not be available. To make DAYOFWEEK_ISO() and WEEK_ISO() functions available in such a database, use the db2updb system command.

System functions

The system functions in this section are supported by CLI and defined by ODBC using vendor escape clauses.

  • Arguments denoted as exp can be the name of a column, the result of another scalar function, or a literal.
  • Arguments denoted as value can be a literal constant.
Table 4. System scalar functions
System scalar function Description Servers that support the function
DATABASE() Returns the name of the database corresponding to the connection handle (hdbc). (The name of the database is also available via SQLGetInfo() by specifying the information type SQL_DATABASE_NAME.) Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
IFNULL( exp, value ) If exp is null, value is returned. If exp is not null, exp is returned. The possible data type(s) of value must be compatible with the data type of exp. Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i
USER() Returns the user's authorization name. (The user's authorization name is also available via SQLGetInfo() by specifying the information type SQL_USER_NAME.) Db2, Db2 for z/OS, Db2 Server for VM and VSE, IBM Db2 for IBM i

Conversion function

The conversion function is supported by CLI and defined by ODBC using vendor escape clauses.

Each driver and data source determines which conversions are valid between the possible data types. As the driver translates the ODBC syntax into native syntax it will reject the conversions that are not supported by the data source, even if the ODBC syntax is valid.

Use the function SQLGetInfo() with the appropriate convert function masks to determine which conversions are supported by the data source.

Table 5. Conversion Function
Conversion scalar function Description Servers that support the function
CONVERT( expr_value, data_type )
  • data_type indicates the data type of the converted representation of expr_value, and can be either SQL_CHAR or SQL_DOUBLE.
  • expr_value is the value to convert. It can be of various types, depending on the conversions supported by the driver and data source. Use the function SQLGetInfo() with the appropriate convert function masks to determine which conversions are supported by the data source.
 Db2