| Scalar functions are applied to a column or expression and operate on a single value. Contrast this with the column functions, which are applied to a set of data and return only a single result. There are more than 110 scalar functions, each of which can be applied to a column value or expression. NOTE DB2 V6 significantly improved IBM's support for built-in scalar functions. Prior to DB2 V6 there were only 22 built-in scalar functions. Additional built-in functions were added for DB2 V7 and V8, as well.
The result of a scalar function is a transformed version of the column or expression being operated on. The transformation of the value is based on the scalar function being applied and the value itself. Consult the following descriptions of the DB2 scalar functions: | ABSVAL or ABS | Converts a value of any numeric data type to its absolute value. | | ACOS | Returns the arc-cosine of the argument as an angle expressed in radians. | |  ADD_MONTHS | Returns a date value that is the result of adding the second expression (which is a duration) to the first expression (which is a date). | | ASIN | Returns the arc-sine of the argument as an angle expressed in radians. | | ATAN | Returns the arc-tangent of the argument as an angle expressed in radians. | | ATANH | Returns the hyperbolic arc-tangent of the argument as an angle expressed in radians. | | ATAN2 | Returns the arc-tangent of the specified x and y coordinates as an angle expressed in radians. | | BLOB | Converts a string or ROWID data type into a value of data type BLOB . | | CCSID_ENCODING | Returns the encoding scheme of the specified CCSID . The result will be one of the following: ASCII , EBCDIC , UNICODE , or UNKNOWN . | | CEILING or CEIL | Converts the argument, represented as any numeric data type, to the smallest integer value greater than or equal to the argument value. | | CHAR | Converts a DB2 date, time, timestamp, ROWID , floating point, integer, or decimal value to a character value. For example SELECT CHAR(HIREDATE, USA) FROM DSN8810.EMP WHERE EMPNO = '000140';
This SQL statement returns the value for HIREDATE , in USA date format, of the employee with the EMPNO of '000140' . | | CLOB | Converts a string or ROWID data type into a value of data type CLOB . | | COALESCE | For nullable columns , returns a value instead of a null (equivalent to VALUE function). | | CONCAT | Converts two strings into the concatenation of the two strings. | | COS | Returns the cosine of the argument as an angle expressed in radians. | | COSH | Returns the hyperbolic cosine of the argument as an angle expressed in radians. | | DATE | Converts a value representing a date to a DB2 date. The value to be converted can be a DB2 timestamp, a DB2 date, a positive integer, or a character string. | | DAY | Returns the day portion of a DB2 date or timestamp. | | DAYOFMONTH | Similar to DAY except DAYOFMONTH cannot accept a date duration or time duration as an argument. | | DAYOFWEEK | Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the day of the week. The value 1 represents Sunday, 2 Monday, 3 Tuesday, 4 Wednesday, 5 Thursday, 6 Friday, and 7 Saturday. | | DAYOFWEEK_ISO | Similar to the DAYOFWEEK function, but results in different numbers to represent the day of the week. Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the day of the week. The value 1 represents Monday, 2 Tuesday, 3 Wednesday, 4 Thursday, 5 Friday, 6 Saturday, and 7 Sunday. | | DAYOFYEAR | Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the day within the year. The value 1 represents January 1 st , 2 January 2 nd , and so on. | | DAYS | Converts a DB2 date or timestamp into an integer value representing one more than the number of days since January 1, 0001. | | DBCLOB | Converts a string or ROWID data type into a value of data type DBCLOB . | | DECIMAL or DEC | Converts any numeric value, or character representation of a numeric value, to a decimal value. | |  DECRYPT_BIT | Decrypts an encrypted column into a binary value using a user -provided encryption password. | | DECRYPT_CHAR | Decrypts an encrypted column into a character value using a user-provided encryption password. | | DECRYPT_DB | Decrypts an encrypted column into a variable graphic value using a user-provided encryption password. | | DEGREES | Returns the number of degrees for the number of radians supplied as an argument. | | DIGITS | Converts a number to a character string of digits. Be aware that the DIGITS function will truncate the negative sign for negative numbers. | | DOUBLE or FLOAT | Converts any numeric value, or character representation of a numeric value, into a double precision floating point value. Another synonym for this function is DOUBLE-PRECISION . | | ENCRYPT_TDES | Uses the Triple DES encryption algorithm to encrypt a column in a table using a user-provided encryption password. | | EXP | Returns the exponential function of the numeric argument. The EXP and LOG functions are inverse operations. | | FLOOR | Converts the argument, represented as any numeric data type, to the largest integer value less than or equal to the argument value. | | GENERATE_UNIQUE | Generates a CHAR(13) FOR BIT DATA value that is unique across the Sysplex. | | GETHINT | Obtains a hint to help remember the encryption password. | | GETVARIABLE | Retrieves session variable values. Details on this function's operation can be found in Chapter 10, "DB2 Security and Authorization." | | GRAPHIC | Converts a string data type into a value of data type GRAPHIC . | | GREATEST | Returns the maximum value in a supplied set of values. The argument values can be of any built-in data type other than CLOB , DBCLOB , BLOB , or ROWID . | | HEX | Converts any value other than a long string to hexadecimal. | | HOUR | Returns the hour portion of a time, a timestamp, or a duration. | | IDENTITY_VAL_LOCAL() | Returns the most recently assigned value for an identity column. (No input parameters are used by this function.) | | IFNULL | Returns the first argument in a set of two arguments that is not null. For example SELECT EMPNO, IFNULL(WORKDEPT, 'N/A') FROM DSN8810.EMP;
This SQL statement returns the value for WORKDEPT for all employees , unless WORKDEPT is null, in which case it returns the string 'N/A' . | | INSERT | Accepts four arguments. Returns a string with the first argument value inserted into the fourth argument value at the position specified by the second argument value. The third argument value indicates the number of bytes to delete (starting at the position indicated by the third argument value). For example SELECT INSERT('FLAMING', 2, 1, 'R') FROM SYSIBM.SYSDUMMY1;
| | | This SQL statement returns the value 'FLAMING' . Here is another example SELECT INSERT('BOSTON CHOWDER', 8, 0, 'CLAM ') FROM SYSIBM.SYSDUMMY1;
This SQL statement returns the value 'BOSTON CLAM CHOWDER' . |
CAUTION Both the value of the argument being inserted into, and the value of the argument that is being inserted, must have the same string data type. That is, both expressions must be character strings, or both expressions must be graphic strings. If the expressions are character strings, neither can be a CLOB . If the expressions are graphic strings, neither can be a DBCLOB .
| INTEGER or INT | Converts any number, or character representation of a number, to an integer by truncating the portion of the number to the right of the decimal point. If the whole number portion of the number is not a valid integer (for example, the value is out of range), an error results. | | JULIAN_DAY | Converts a DB2 date or timestamp, or character representation of a date or timestamp, into an integer value representing the number of days from January 1, 4712 B.C. to the date specified in the argument. |
NOTE January 1, 4712 B.C. is the start date of the Julian calendar.
| LAST_DAY | Returns the last day of the month for the specified DB2 date or timestamp, or character representation of a date or timestamp. | | LEAST | Returns the minimum value in a supplied set of values. The argument values can be of any built-in data type other than CLOB , DBCLOB , BLOB , or ROWID . | | LEFT | Returns a string containing only the leftmost characters of the string in the first argument, starting at the position indicated by the second argument. For example SELECT LEFT('THIS IS RETURNED', 4) FROM SYSIBM.SYSDUMMY1;
This SQL statement returns 'THIS' , which is the four leftmost characters of the first argument. | | LENGTH | Returns the length of any column, which may be null. Does not include the length of null indicators or variable character-length control values, but does include trailing blanks for character columns. | | LOCATE | Returns the position of the first occurrence of the first string the second string. For example SELECT LOCATE('I', 'CRAIG MULLINS') FROM SYSIBM.SYSDUMMY1;
| | | This SQL statement returns the value 4 , because the value 'I' first appears in position four within the searched string. It also appears in the 11 th position, but that is of no concern to the LOCATE function. Optionally, a third argument can be supplied indicating where the search should start. For example SELECT LOCATE('I', 'CRAIG MULLINS', 7) FROM SYSIBM.SYSDUMMY1;
This SQL statement returns the value 11 , because after position 7, the value 'I' first appears in the 11 th position. When the third argument is not specified, LOCATE defaults to the beginning of the second string. | | LOG or LN | Returns the natural logarithm of the numeric argument. The EXP and LOG functions are inverse operations. | | LOG10 | Returns the base 10 logarithm of the numeric argument. | | LOWER or LCASE | Converts a character string into all lowercase characters. | | LTRIM | Removes the leading blanks from a character string. | | MAX | Returns the maximum value in a supplied set of values. The argument values can be of any built-in data type other than CLOB , DBCLOB , BLOB , or ROWID . (This scalar function is not the same as the MAX column function discussed earlier.) GREATEST is a synonym for the MAX scalar function. | | MICROSECOND | Returns the microsecond component of a timestamp or the character representation of a timestamp. | | MIDNIGHT_SECONDS | Returns the number of seconds since midnight for the specified argument, which must be a time, timestamp, or character representation of a time or timestamp. | | MIN | Returns the minimum value in a supplied set of values. The argument values can be of any built-in data type other than CLOB , DBCLOB , BLOB , or ROWID . (This scalar function is not the same as the MIN column function discussed earlier.) LEAST is a synonym for MIN . | | MINUTE | Returns the minute portion of a time, a timestamp, a character representation of a time or timestamp, or a duration. | | MOD | Returns the remainder of the division of the first argument by the second argument. Both arguments must be numeric. | | MONTH | Returns the month portion of a date, a timestamp, a character representation of a date or timestamp, or a duration. | | MULTIPLY_ALT | Can be used as an alternative to the multiplication operator. This function returns a decimal value that is the product of multiplying the two arguments together. | | NEXT_DAY | Returns a timestamp indicating the first day of the week as specified in the second argument that is later than the date expression specified in the first argument. Valid values for the second argument are text representations of the days of the week; that is, MONDAY , TUESDAY , and so on. For example SELECT NEXT_DAY(CURRENT DATE, 'FRIDAY') FROM SYSIBM.SYSDUMMY1;
This SQL statement returns a timestamp specifying the first Friday after today. | | NULLIF | Returns a null when two specified expressions are equal; if not equal, the first expression is returned. | | POSSTR | Similar to the LOCATE function, but with the arguments reversed . POSSTR returns the position of the first occurrence of the second argument within the first argument. For example SELECT POSSTR('DATABASE ADMINISTRATION', 'ADMIN') FROM SYSIBM.SYSDUMMY1;
This SQL statement returns the value 10 ; the value 'ADMIN' first appears in the 10 th position. | | POWER | Returns the value of the first argument raised to the power of the second argument. | | QUARTER | Converts a date, timestamp, or string representation of a date or timestamp into an integer that represents the quarter within the year. The value 1 represents first quarter, 2 second quarter, 3 third quarter, and 4 fourth quarter. | | RADIANS | Returns the number of radians for the numeric argument expressed in degrees. | | RAND | Returns a random floating-point number between and 1 . Optionally, an integer value can be supplied as a seed value for the random value generator. For example SELECT (RAND() * 100) FROM SYSIBM.SYSDUMMY1;
This SQL statement returns a random number between and 100 . | | REAL | Converts any numeric value, or character representation of a numeric value, into a single precision floating point value. | | REPEAT | Returns a character string that consists of the first argument repeated the number of times specified in the second argument. For example SELECT REPEAT('HO ', 3) FROM SYSIBM.SYSDUMMY1;
This SQL statement returns the character string 'HO HO HO ' . | | REPLACE | Returns a character string with the value of the second argument replaced by each instance of the third argument in the first argument. For example SELECT REPLACE('BATATA', 'TA', 'NA') FROM SYSIBM.SYSDUMMY1;
This SQL statement replaces all instances of 'TA' with 'NA' changing the character string 'BATATA' into 'BANANA' . |
CAUTION Neither the first nor the second argument may be empty strings. The third argument, however, can be an empty string. If the third argument is an empty string, the REPLACE function will simply replace each instance of the second argument with an empty string.
| RIGHT | Returns a string containing only the rightmost characters of the string in the first argument, starting at the position indicated by the second argument. For example SELECT RIGHT('RETURN ONLY THIS', 4) FROM SYSIBM.SYSDUMMY1;
This SQL statement returns 'THIS' , which is the four rightmost characters of the first argument. | | ROUND | Rounds the first numeric argument to the number of places specified in the second argument. | | ROUND_TIMESTAMP | Rounds the timestamp value specified in the first argument based on the unit specified in the second argument. The timestamp can be rounded to the nearest year, quarter, month, week, day, hour, minute, or second. |
NOTE Table 3.1 highlights the valid unit arguments for both the ROUND_TIMESTAMP and TRUNC_TIMESTAMP function.
| ROWID | Casts the specified argument to a ROWID data type. Although the argument can be any character string, it should be a row ID value that was previously generated by DB2. Otherwise , the value may not be an accurate DB2 ROWID . | | RTRIM | Removes the trailing blanks from a character string. | | SECOND | Returns the seconds portion of a time, a timestamp, a character representation of a time or timestamp, or a duration. | | SIGN | Returns a value that indicates the sign of the numeric argument. The returned value will be “1 if the argument is less than zero, +1 if the argument is greater than zero, and if the argument equals zero. | | SIN | Returns the sine of the argument as an angle expressed in radians. | | SINH | Returns the hyperbolic sine of the argument as an angle expressed in radians. | | SMALLINT | Converts any number, or character representation of a number, to an integer by truncating the portion of the number to the right of the decimal point. If the whole number portion of the number is not a valid integer (for example, the value is out of range), an error results. | | SPACE | Returns a string of blanks whose length is specified by the numeric argument. The string of blanks is an SBCS character string. | | SQRT | Returns the square root of the numeric argument. | | STRIP | Removes leading, trailing, or both leading and trailing blanks (or any specific character) from a string expression. | | SUBSTR | Returns the specified portion of a character column from any starting point to any ending point. | | TAN | Returns the tangent of the argument as an angle expressed in radians. | | TANH | Returns the hyperbolic tangent of the argument as an angle expressed in radians. | | TIME | Converts a value representing a valid time to a DB2 time. The value to be converted can be a DB2 timestamp, a DB2 time, or a character string. | | TIMESTAMP | Obtains a timestamp from another timestamp, a valid character-string representation of a timestamp, or a combination of date and time values. | | TIMESTAMP_FORMAT | Returns a DB2 timestamp for the data in the first argument (which must be a character expression) based on the formatting specified in the second argument. For example SELECT TIMESTAMP_FORMAT('2004-12-15 23:59:59',  'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;
This SQL statement converts the non-standard timestamp representation into a standard DB2 timestamp. | | TRANSLATE | Translates characters from one expression to another. There are two forms of the TRANSLATE function. If only one argument is specified, the character string is translated to uppercase. Alternately, three arguments can be supplied. In this case, the first argument is transformed by replacing the character string specified in the third argument with the character string specified in the second argument. For example SELECT TRANSLATE('BACK', 'R', 'C') FROM SYSIBM.SYSDUMMY1;
This SQL statement returns 'BARK' , because the character string 'C' is replaced with the character string 'R' . | | | Optionally, a fourth argument can be specified. This is the pad character. If the length of the second argument is less than the length of the third argument, the second argument will be padded with the pad character (or blanks) to make up the difference in size . For example SELECT TRANSLATE('BACK', 'Y', 'ACK', '.') FROM SYSIBM.SYSDUMMY1;
This SQL statement returns 'BY..' , because the character string 'ACK' is replaced with the character string 'Y' , and is padded with '.' characters to make up the difference in size. The string to be translated must be a character string not exceeding 255 bytes or a graphic string of no more than 127 bytes. The string cannot be a CLOB or DBCLOB . | | TRUNCATE or TRUNC | Converts the first numeric argument by truncating it to the right of the decimal place by the integer number specified in the second numeric argument. For example SELECT TRUNC(3.014015,2) FROM SYSIBM.SYSDUMMY1;
This SQL statement returns the number 3.010000 , because the second argument specified that only 2 significant digits are required. The rest was truncated. | | TRUNC_TIMESTAMP | Truncates the timestamp value specified in the first argument based on the unit specified in the second argument. The timestamp can be truncated by year, quarter, month, week, day, hour, minute, or second. |
NOTE Table 3.1 highlights the valid unit arguments for both the ROUND_TIMESTAMP and TRUNC_TIMESTAMP function.
| UPPER or UCASE | Converts a character string into all uppercase characters. | | VALUE | For nullable columns, returns a value instead of a null (equivalent to the COALESCE function). | | VARCHAR | Converts a character string, date, time, timestamp, integer, decimal, floating point, or ROWID value into a corresponding variable character string representation. | | VARCHAR_FORMAT | Returns the character representation for the timestamp expression specified in the first argument based on the formatting specified in the second argument. For example SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP ,'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;
| | | This SQL statement converts a standard timestamp into a non-standard character representation of that timestamp. | | VARGRAPHIC | Converts a character string to a graphic string. | | WEEK | Returns an integer between 1 and 54 based on the week of the year in which a date, timestamp, or string representation of a date or timestamp falls . The assumption is that a week begins on Sunday and ends on Saturday. The value 1 represents the first week, 2 the second week, and so on. | | WEEK_ISO | Returns an integer between 1 and 53 based on the week of the year in which a date, timestamp, or string representation of a date or timestamp falls. The assumption is that a week begins on Monday and ends on Sunday. Week 1 is the first week of the year to contain a Thursday. So, it is possible to have up to three days at the beginning of the year appear as the last week of the previous year, or to have up to three days at the end of a year appear as the first week of the next year. | | YEAR | Returns the year portion of a date, a timestamp, or a duration. |
Table 3.1. Units for Rounding and Truncating TIMESTAMP s | Unit | Definition | Explanation | | CC | Century | Rounds up to the next century starting in the 50 th year of the century or truncates to the first day of the current century. SCC can be used as a synonym for CC . | | YYYY | Year | Rounds up to the next year starting on July 1 st or truncates to the first day of the current year. The following can be used as synonyms in place of YYYY : SYYY , YEAR , SYEAR , YYY , YY , and Y . | | IYYY | ISO Year | Provides the same functionality as YYYY . The following can be used as synonyms in place of IYYY : IYY , IY , and I . | | Q | Quarter | Rounds up to the next quarter starting on the sixteenth day of the second month of the quarter or truncates to the first day of the current quarter. | | MM | Month | Rounds up to the next month on the sixteenth day of the month or truncates to the first day of the current month. The following can be used as synonyms in place of MM : MONTH , MON , and RM . | | WW | Week | Rounds up to the next week on the twelfth hour of the third day of the week (with respect to the first day of the year) or truncates to the first day of the current week. | | IW | ISO Week | Rounds up to the next week on the twelfth hour of the third day of the week (with respect to the first day of the ISO year) or truncates to the first day of the current ISO week. | | W | Week | Rounds up to the next week on the twelfth hour of the third day of the week (with respect to the first day of the month) or truncates to the first day of the current week (also with respect to the first day of the month). | | DDD | Day | Rounds up to the next day on the twelfth hour of the day or truncates to the beginning of the current day. DD and J can be used as synonyms in place of DDD . | | DAY | Start Day | Differs from DDD by rounding to the starting day of a week. Rounds up to the next week on the twelfth hour of the third day of the week, otherwise it truncates to the starting day of the current week. DY and D can be used as synonyms in place of DAY . | | HH | Hour | Rounds up to the next hour at 30 minutes or truncates to the beginning of the current hour. HH12 and HH24 can be used as synonyms in place of HH . | | MI | Minute | Rounds up to the next minute at 30 seconds or truncates to the beginning of the current minute. | | SS | Second | Rounds up to the next second at 500000 microseconds or truncates to the beginning of the current second. |
Some rules for the scalar functions follow: -
Scalar functions can be executed in the select-list of the SQL SELECT statement or as part of a WHERE or HAVING clause. -
A scalar function can be used wherever an expression can be used. -
The argument for a scalar function can be a column function. |
