Home > Articles

Using DB2 Functions

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

Scalar Functions

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 1st, 2 January 2nd, 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_

Returns the most recently assigned value for an identity column. (No input LOCAL()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 11th 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 11th 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 10th 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 0 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 0 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 0 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 TIMESTAMPs

Unit

Definition

Explanation

CC

Century

Rounds up to the next century starting in the 50th 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 1st 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.

  • + Share This
  • 🔖 Save To Your Account