A scalar function can be used wherever an expression can be used.
The restrictions on the use of column functions do not apply to scalar functions because a scalar function is applied to a single value rather than a set of values. For example, the argument of a scalar function can be a function. However, the restrictions that apply to the use of expressions and column functions also apply when an expression or column function is used within a scalar function. For example, the argument of a scalar function can be a column function only if a column function is allowed in the context in which the scalar function is used.
For example, the result of the following SELECT statement has as many rows as there are employees in department D01:
SELECT EMPNO, LASTNAME, YYEAR(CURRENT DATE - BIRTHDATE) FROM CORPDATA.EMPLOYEE WHERE WORKDEPT = 'D01'
>>--+-ABS----+---(--expression--)------------------------------><
'-ABSVAL-'
|
The ABS and ABSVAL functions return the absolute value of a number.
The argument must be a number.
The data type and length attribute of the result are the same as the data type and length attribute of the argument value, except that the result is a large integer if the argument value is a small integer, and the result is double-precision floating point if the argument value is single-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable PROFIT is a large integer with a value of -50000.
ABSVAL(:PROFIT)
Returns the value 50000.
>>-ACOS--(--expression--)-------------------------------------->< |
The ACOS function returns the arc cosine of a number, in radians. The ACOS and COS functions are inverse operations.
The argument must be a number whose value is greater than or equal to -1 and less than or equal to 1.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is in the range (0, &pi.).
Assume the host variable ACOSINE is a decimal (10,9) host variable with a value of 0.070737202.
ACOS(:ACOSINE)
Returns the approximate value 1.49.
>>-ANTILOG--(--expression--)----------------------------------->< |
The ANTILOG function returns the anti-logarithm (base 10) of a number. The ANTILOG and LOG functions are inverse operations.
The argument must be a number.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable ALOG is a decimal (10,9) host variable with a value of 1.499961866.
ANTILOG(:ALOG)
Returns the approximate value 31.62.
>>-ASIN--(--expression--)-------------------------------------->< |
The ASIN function returns the arc sine of a number, in radians. The ASIN and SIN functions are inverse operations.
The argument must be a number whose value is greater than or equal to -1 and less than or equal to 1.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is in the range (-&pi./2, &pi./2).
Assume the host variable ASINE is a decimal (10,9) host variable with a value of 0.997494987.
ASIN(:ASINE)
Returns the approximate value 1.50.
>>-ATAN--(--expression--)-------------------------------------->< |
The ATAN function returns the arc tangent of a number, in radians. The ATAN and TAN functions are inverse operations.
The argument must be a number.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is in the range (-&pi./2, &pi./2).
Assume the host variable ATANGENT is a decimal (10,8) host variable with a value of 14.10141995.
ATAN(:ATANGENT)
Returns the approximate value 1.50.
>>-ATANH--(--expression--)------------------------------------->< |
The ATANH function returns the hyperbolic arc tangent of a number, in radians. The ATANH and TANH functions are inverse operations.
The argument must be a number whose value is greater than -1 and less than 1.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable HATAN is a decimal (10,9) host variable with a value of 0.905148254.
ATANH(:HATAN)
Returns the approximate value 1.50.
>>---BIGINT----(--+-numeric-expression---+---)-----------------><
'-character-expression-'
|
The BIGINT function returns a big integer representation of:
If the argument is a numeric-expression, the result is the same number that would occur if the argument were assigned to a big integer column or variable. If the whole part of the argument is not within the range of integers, an error occurs. The fractional part of the argument is truncated.
If the argument is a character-expression, the result is the same number that would result from CAST( character-expression AS BIGINT). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming a floating-point, integer, or decimal constant. If the whole part of the argument is not within the range of integers, an error occurs. Any fractional part of the argument is truncated.
The result of the function is a big integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the EMPLOYEE table, select the EMPNO column in big integer form for further processing in the application..
SELECT BIGINT(SALARY) FROM EMPLOYEE
>>-BLOB--(--string-expression--+-------------+---)-------------><
'-,--integer--'
|
The BLOB function returns a BLOB representation of a string of any type.
The result of the function is a BLOB. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
If the second argument is not specified:
The actual length of the result is the minimum of the length attribute of the result and the actual length of the expression (or twice the length of the expression when the input is graphic data). If the length of the string-expression is greater than the length attribute of the result, truncation is performed and a warning is returned.
The following function returns a BLOB for the string 'This is a BLOB'.
SELECT BLOB('This is a BLOB') FROM T1
The following function returns a BLOB for the large object that is identified by locator myclob_locator.
SELECT BLOB(:myclob_locator) FROM T1
Assume that a table has a BLOB column named TOPOGRAPHIC_MAP and a VARCHAR column named MAP_NAME. Locate any maps that contain the string 'Pellow Island' and return a single binary string with the map name concatenated in front of the actual map. The following function returns a BLOB for the large object that is identified by locator myclob_locator.
SELECT BLOB( MAP_NAME CONCAT ': ' CONCAT TOPOGRAPHIC_MAP ) FROM ONTARIO_SERIES_4 WHERE TOPOGRAPHIC_MAP LIKE '%Pellow Island%'
>>--+-CEILING-+--(--numeric-expression--)----------------------><
'-CEIL----'
|
The CEIL or CEILING function returns the smallest integer value that is greater than or equal to numeric-expression.
The argument is an expression that returns a value of any built-in numeric data type.
The result of the function has the same data type and length attribute of the argument except that:
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
SELECT CEIL(MAX(SALARY)/12 FROM CORPDATA.EMPLOYEEThis example returns 4396.00 because the highest paid employee is Christine Haas who earns $52750.00 per year. Her average monthly salary before applying the CEIL function is 4395.83.
SELECT CEILING( 3.5),
CEILING( 3.1),
CEILING(-3.1),
CEILING(-3.5),
FROM TABLEX
This example returns:
4.0 4.0 -3.0 -3.0respectively.
Datetime to Character
>>-CHAR---(--datetime-expression--+-------------+---)----------><
'-,--+-ISO-+--'
+-USA-+
+-EUR-+
'-JIS-'
Character to Character
>>-CHAR---(--character-expression--+-------------+---)---------><
'-,--integer--'
Integer to Character >>-CHAR---(--integer-expression--)----------------------------->< Decimal to Character
>>-CHAR---(--decimal-expression--+-----------------------+---)--><
'-,--decimal-character--'
Floating-point to Character
>>-CHAR--------------------------------------------------------->
>----(--floating-point-expression--+-----------------------+---)--><
'-,--decimal-character--'
|
The CHAR function returns a string representation of:
| Note: | The CAST expression can also be used to return a string expression. For more information, see "CAST Specification" under Expressions. |
The result of the function is a fixed-length character string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Datetime to Character
The DATFMT and DATSEP parameters are specified on the Create SQL Program (CRTSQLxxx), Run SQL Statements (RUNSQLSTM), and Start SQL (STRSQL) commands. The SET OPTION statement can be used to specify the DATFMT and DATSEP parameters within the source of a program containing embedded SQL.
The TIMFMT and TIMSEP parameters are specified on the Create SQL Program (CRTSQLxxx), Run SQL Statements (RUNSQLSTM), and Start SQL (STRSQL) commands. The SET OPTION statement can be used to specify the TIMFMT and TIMSEP parameters within the source of a program containing embedded SQL.
The result is the character-string representation of the timestamp. The length of the result is 26.
The CCSID of the string is the default SBCS CCSID at the current server.
Character to Character
If the second argument is not specified:
The actual length is the same as the length attribute of the result. If the length of the character-expression is less than the length of the result, the result is padded with blanks up to the length of the result. If the length of the character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the truncated characters were all blanks.
The CCSID of the string is the CCSID of the character-expression.
Integer to Character
The result is the character string representation of the argument in the form of an SQL integer constant. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. It is left justified.
The length of the result is 6. If the number of characters in the result is less than 6, then the result is padded on the right with blanks.
The length of the result is 11. If the number of characters in the result is less than 11, then the result is padded on the right with blanks.
The length of the result is 20. If the number of characters in the result is less than 20, then the result is padded on the right with blanks.
The CCSID of the string is the default SBCS CCSID at the current server.
Decimal to Character
The result is a fixed-length character string representation of the argument. The result includes a decimal character and up to p digits, where p is the precision of the decimal-expression with a preceding minus sign if the argument is negative. Leading zeros are not returned. Trailing zeros are returned. The length of the result is 2+p where p is the precision of the decimal-expression. This means that a positive value will always include one trailing blank.
The CCSID of the string is the default SBCS CCSID at the current server.
Floating-point to Character
The result is a fixed-length character string representation of the argument in the form of a floating-point constant. The length of the result is 24. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit. If the argument is zero, the result is 0E0. Otherwise, the result includes the smallest number of characters that can be used to represent the value of the argument such that the mantissa consists of a single digit other than zero followed by a period and a sequence of digits. If the number of characters in the result is less than 24, then the result is padded on the right with blanks.
The CCSID of the string is the default SBCS CCSID at the current server.
Assume the column PRSTDATE has an internal value equivalent to 1988-12-25. The date format is *MDY and the date separator is a slash (/).
CHAR(PRSTDATE, USA)
Results in the value '12/25/1988'.
CHAR(PRSTDATE)
Results in the value '12/25/88'.
Assume the column STARTING has an internal value equivalent to 17.12.30, the host variable HOUR_DUR (decimal(6,0)) is a time duration with a value of 050000 (that is, 5 hours).
CHAR(STARTING, USA)
Results in the value '5:12 PM'.
CHAR(STARTING + :HOUR_DUR, USA)
Results in the value '10:12 PM'.
Assume the column RECEIVED (timestamp) has an internal value equivalent to the combination of the PRSTDATE and STARTING columns.
CHAR(RECEIVED)
Results in the value '1988-12-25-17.12.30.000000'.
Use the CHAR function to make the type fixed length character and reduce the length of the displayed results to 10 characters for the LASTNAME column (defined as VARCHAR(15)) of the EMPLOYEE table.
CHAR(LASTNAME,10)
For rows having a LASTNAME with a length greater than 10 characters (excluding trailing blanks), a warning that the value is truncated is returned.
Use the CHAR function to return the values for EDLEVEL (defined as SMALLINT) as a fixed length string.
CHAR(EDLEVEL)
An EDLEVEL of 18 would be returned as the CHAR(6) value '18' (18 followed by 4 blanks).
Assume that the STAFF table has a SALARY column defined as decimal with precision of 9 and scale of 2. The current value is 18357.50 and it is to be returned with a comma as the decimal character (18357,50).
CHAR(SALARY, ',')
returns the value '18357,50' followed by 3 blanks.
Assume the same SALARY column subtracted from 20000.25 is to be returned with the default decimal character, and the default is period.
CHAR(20000.25 - SALARY)
returns the value '-1642.75' followed by 3 blanks.
Assume a host variable, SEASONS_TICKETS, has an integer data type and a value of 10000.
CHAR(DECIMAL(:SEASONS_TICKETS,7,2))
Results in the character value '10000.00'.
Assume a host variable, DOUBLE_NUM, has a double precision floating-point data type and a value of -987.654321E-35.
CHAR(:DOUBLE_NUM)
Results in the character value '-9.8765432100000002E-33 '.
>>-+-CHARACTER_LENGTH-+--(--expression--)---------------------->< '-CHAR_LENGTH------' |
The CHARACTER_LENGTH or CHAR_LENGTH function returns the length of a string expression. See LENGTH for a similar function.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the number of characters in the argument (not the number of bytes). A single character is either an SBCS or DBCS character. The length of strings includes trailing blanks. The length of a varying-length string is the actual length, not the maximum length.
Assume the host variable ADDRESS is a varying-length character string with a value of '895 Don Mills Road'.
CHARACTER_LENGTH(:ADDRESS)
Returns the value 18.
Character to CLOB
>>-CLOB (--character-expression--------------------------------->
>-----+-----------------------------------+--)-----------------><
'-,--+-length--+---+-------------+--'
'-DEFAULT-' '-,--integer--'
Graphic to CLOB
>>-CLOB (--graphic-expression----------------------------------->
>-----+-----------------------------------+--)-----------------><
'-,--+-length--+---+-------------+--'
'-DEFAULT-' '-,--integer--'
Integer to CLOB >>-CLOB---(--integer-expression--)----------------------------->< Decimal to CLOB
>>-CLOB---(--decimal-expression--+-----------------------+---)--><
'-,--decimal-character--'
Floating-point to CLOB
>>-CLOB--------------------------------------------------------->
>----(--floating-point-expression--+-----------------------+---)--><
'-,--decimal-character--'
|
The CLOB function returns a character-string representation of:
The result of the function is a varying-length string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Character to CLOB
If the second argument is not specified or DEFAULT is specified:
The actual length of the result is the minimum of the length attribute of the result and the actual length of character-expression. If the length of the character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the truncated characters were all blanks.
If the third argument is not specified then:
Graphic to CLOB
If the second argument is not specified or DEFAULT is specified, the length attribute of the result is determined as follows (where n is the length attribute of the first argument):
The actual length of the result is the minimum of the length attribute of the result and the actual length of graphic-expression. If the length of the graphic-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the truncated characters were all blanks.
If the third argument is not specified, the CCSID of the result is the default CCSID at the current server. If the default CCSID is mixed data, then the result is mixed data. If the default CCSID is SBCS data, then the result is SBCS data.
Integer to CLOB
The result is a varying-length character string of the argument in the form of an SQL integer constant. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. It is left justified.
The actual length of the result is the smallest number of characters that can be used to represent the value of the argument. Leading zeroes are not included. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit.
The CCSID of the result is the default SBCS CCSID at the current server.
Decimal to CLOB
The result is a varying-length character string representation of the argument. The result includes a decimal character and up to p digits, where p is the precision of the decimal-expression with a preceding minus sign if the argument is negative. Leading zeros are not returned. Trailing zeros are returned.
The length attribute of the result is 2+p where p is the precision of the decimal-expression. The actual length of the result is the smallest number of characters that can be used to represent the result, except that trailing characters are included. Leading zeros are not included. If the argument is negative, the result begins with a minus sign. Otherwise, the result begins with a digit.
The CCSID of the result is the default SBCS CCSID at the current server.
Floating-point to CLOB
The result is a varying-length character string representation of the argument in the form of a floating-point constant.
The length attribute of the result is 24. The actual length of the result is the smallest number of characters that can represent the value of the argument such that the mantissa consists of a single digit other than zero followed by the decimal-character and a sequence of digits. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If the argument is zero, the result is 0E0.
The CCSID of the result is the default SBCS CCSID at the current server.
The following function returns a CLOB for the string 'This is a CLOB'.
SELECT CLOB('This is a CLOB') FROM T1
.------------------.
V |
>>-COALESCE---(--expression-----,--expression---+---)----------><
|
The COALESCE function returns the first argument that is not null.
The arguments must be compatible. Character-string arguments are compatible with datetime values. For more information on data type compatibility, see Assignments and Comparisons.
The arguments are evaluated in the order in which they are specified, and the result of the function is the first argument that is not null. The result can be null only if all arguments can be null, and the result is null only if all arguments are null. The selected argument is converted, if necessary, to the attributes of the result. There must be two or more arguments. Arguments other than the first argument may be parameter markers.
The attributes of the result are derived from all the operands as explained in "Rules for Result Data Types".
When selecting all the values from all the rows in the DEPARTMENT table, if the department manager (MGRNO) is missing (that is, null), then return a value of 'ABSENT'.
SELECT DEPTNO, DEPTNAME, COALESCE(MGRNO, 'ABSENT'), ADMRDEPT FROM DEPARTMENT
When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is null), then return a value of zero.
SELECT EMPNO, COALESCE(SALARY,0) FROM EMPLOYEE
>>-CONCAT--(--expression--,--expression--)--------------------->< |
The CONCAT function is identical to the CONCAT operator. For more information, see "With the Concatenation Operator".
Concatenate the column FIRSTNME with the column LASTNAME.
CONCAT(FIRSTNME, LASTNAME)
>>-COS--(--expression--)--------------------------------------->< |
The COS function returns the cosine of a number. The COS and ACOS functions are inverse operations.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable COSINE is a decimal (2,1) host variable with a value of 1.5.
COS(:COSINE)
Returns the approximate value 0.07.
>>-COSH--(--expression--)-------------------------------------->< |
The COSH function returns the hyperbolic cosine of a number.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable HCOS is a decimal (2,1) host variable with a value of 1.5.
COSH(:HCOS)
Returns the approximate value 2.35.
>>-COT--(--expression--)--------------------------------------->< |
The COT function returns the cotangent of a number.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable COTAN is a decimal (2,1) host variable with a value of 1.5.
COT(:COTAN)
Returns the approximate value 0.07.
>>-CURDATE--(--)----------------------------------------------->< |
The CURDATE function returns a date based on a reading of the time-of-day clock when the SQL statement is executed at the current server. The value returned by the CURDATE function is the same as the value returned by the CURRENT DATE special register. If this function is used more than once within a single SQL statement, or used with the CURTIME or NOW scalar functions or the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special registers within a single statement, all values are based on a single clock reading.
The data type of the result is a date.
Return the current date based on the time-of-day clock.
CURDATE()
>>-CURTIME--(--)----------------------------------------------->< |
The CURTIME function returns a time based on a reading of the time-of-day clock when the SQL statement is executed at the current server. The value returned by the CURTIME function is the same as the value returned by the CURRENT TIME special register. If this function is used more than once within a single SQL statement, or used with the CURDATE or NOW scalar functions or the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special registers within a single statement, all values are based on a single clock reading.
The data type of the result is a time.
Return the current time based on the time-of-day clock.
CURTIME()
>>-DATE--(--expression--)-------------------------------------->< |
The DATE function returns a date from a value.
The argument must be a timestamp, a date, a positive number less than or equal to 3652059, a valid string representation of a date, or a character string of length 7.
If the argument is a character string of length 7, it must represent a valid date in the form yyyynnn, where yyyy are digits denoting a year, and nnn are digits between 001 and 366 denoting a day of that year.
The result of the function is a date. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the date part of the timestamp.
The result is that date.
The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number.
When a string representation of a date is SBCS data with a CCSID that is not the same as the default CCSID for SBCS data, that value is converted to adhere to the default CCSID for SBCS data before it is interpreted and converted to a date value.
When a string representation of a date is mixed data with a CCSID that is not the same as the default CCSID for mixed data, that value is converted to adhere to the default CCSID for mixed data before it is interpreted and converted to a date value.
DATE(RECEIVED)
Results in an internal representation of '1988-12-25'.
DATE('1988-12-25')
Results in an internal representation of '1988-12-25'.
DATE('25.12.1988')
Results in an internal representation of '1988-12-25'.
DATE(35)
Results in an internal representation of '0001-02-04'.
>>-DAY--(--expression--)--------------------------------------->< |
The DAY function returns the day part of a value.
The argument must be a date, timestamp, date duration, or timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the day part of the value, which is an integer between 1 and 31.
The result is the day part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
SELECT DAY(PRENDATE) INTO :END_DAY FROM PROJECT WHERE PROJNAME = 'WELD LINE PLANNING'
Results in END_DAY being set to 15 when using the sample table.
DAY(DATE1 - DATE2)
Results in the value 15.
>>-DAYOFMONTH--(--expression--)-------------------------------->< |
The DAYOFMONTH function returns the day part of a value.
The argument must be a date or timestamp.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the day part of the value, which is an integer between 1 and 31.
SELECT DAYOFMONTH(PRENDATE) INTO :END_DAY FROM PROJECT WHERE PROJNAME = 'WELD LINE PLANNING'
Results in END_DAY being set to 15 when using the sample table.
>>-DAYOFWEEK--(--expression--)--------------------------------->< |
The DAYOFWEEK function returns an integer between 1 and 7 which represents the day of the week, where 1 is Sunday and 7 is Saturday.
The argument must be a date or a timestamp.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the EMPLOYEE table, set the host variable DAY_OF_WEEK (int) to the day of the week that Christine Haas (EMPNO='000010') started (HIREDATE).
SELECT DAYOFWEEK(HIREDATE)
INTO :DAY_OF_WEEK
FROM EMPLOYEE
WHERE EMPNO = '000010'
Results in DAY_OF_WEEK being set to 6 when using the sample table.
>>-DAYOFYEAR--(--expression--)--------------------------------->< |
The DAYOFYEAR function returns an integer between 1 and 366 which represents the day of the year where 1 is January 1.
The argument must be a date or a timestamp.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the EMPLOYEE table, set the host variable AVG_DAY_OF_YEAR (int) to the average of the day of the year that employees started on (HIREDATE).
SELECT AVG(DAYOFYEAR(HIREDATE)) INTO :AVG_DAY_OF_YEAR FROM EMPLOYEE
Results in AVG_DAY_OF_YEAR being set to 202 when using the sample table.
>>-DAYS--(--expression--)-------------------------------------->< |
The DAYS function returns an integer representation of a date.
The argument must be a date, a timestamp, or a valid string representation of a date.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is 1 more than the number of days from January 1, 0001 to D, where D is the date that would occur if the DATE function were applied to the argument.
SELECT DAYS(PRENDATE) - DAYS(PRSTDATE)
INTO :EDUCATION_DAYS
FROM PROJECT
WHERE PROJNO = 'IF2000'
Results in EDUCATION_DAYS being set to 396 when using the sample table.
SELECT SUM(DAYS(PRENDATE) - DAYS(PRSTDATE)) INTO :TOTAL_DAYS FROM PROJECT WHERE DEPTNO = 'E21'
Results in TOTAL_DAYS being set to 1484 when using the sample table.
>>-DBCLOB--(--expression---------------------------------------->
>-----+-----------------------------------+--)-----------------><
'-,--+-length--+---+-------------+--'
'-DEFAULT-' '-,--integer--'
|
The DBCLOB function returns a DBCLOB representation of a string expression.
The first argument must be a string expression. It cannot be a BLOB. It cannot be CHAR or VARCHAR bit data. It cannot be GRAPHIC or VARGRAPHIC with a CCSID of 65535 unless a third argument is specified.
The second argument, if specified as length, is the length attribute of the result and must be an integer constant between 1 and 7 864 320.
If the second argument is not specified or DEFAULT is specified:
The actual length of the result is the minimum of the length attribute of the result and the actual length of expression. If the length attribute of the resulting DBCLOB is less than the actual length of the first argument, truncation is performed and no warning is returned.
In the following rules, S denotes one of the following:
If the third argument is specified, the CCSID of the result is the third argument. It must be a DBCS or UCS-2 CCSID. The CCSID cannot be 65535.
If the third argument is not specified and the first argument is character, then the CCSID of the result is determined by a mixed CCSID. Let M denote that mixed CCSID. M is determined as follows:
The following table summarizes the result CCSID based on M.
| M | Result CCSID | Description | DBCS Substitution Character |
|---|---|---|---|
| 930 | 300 | Japanese EBCDIC | X'FEFE' |
| 933 | 834 | Korean EBCDIC | X'FEFE' |
| 935 | 837 | S-Chinese EBCDIC | X'FEFE' |
| 937 | 835 | T-Chinese EBCDIC | X'FEFE' |
| 939 | 300 | Japanese EBCDIC | X'FEFE' |
| 5026 | 4396 | Japanese EBCDIC | X'FEFE' |
| 5035 | 4396 | Japanese EBCDIC | X'FEFE' |
If the third argument is not specified and the first argument is not character, then the CCSID of the result is the same as the CCSID of the first argument.
The result of the function is a DBCLOB string. If the expression can be null, the result can be null. If the expression is null, the result is the null value. If the expression is an empty string or the EBCDIC string X'0E0F', the result is an empty string.
If the result is DBCS-graphic data, the equivalence of SBCS and DBCS characters depends on M. Regardless of the CCSID, every double-byte code point in the argument is considered a DBCS character, and every single-byte code point in the argument is considered an SBCS character with the exception of the EBCDIC mixed data shift codes X'0E' and X'0F'.
If the result is UCS-2 graphic data, each character of the argument determines a character of the result. The nth character of the result is the UCS-2 equivalent of the nth character of the argument.
Using the EMPLOYEE table, set the host variable VAR_DESC (vargraphic(24)) to the DBCLOB equivalent of the first name (FIRSTNME) for employee number (EMPNO) '000050'.
SELECT DBCLOB(FIRSTNME) INTO :VAR_DESC FROM EMPLOYEE WHERE EMPNO = '000050'
Numeric to Decimal
>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'
>----(--numeric-expression----+----------------------------------------------+---)->
'-,--precision-integer--+-------------------+--'
'-,--scale-integer--'
>--------------------------------------------------------------><
Character to Decimal
>>-+-DECIMAL-+-------------------------------------------------->
'-DEC-----'
>----(--character-expression----+-------------------------------------------------------------------------+---)->
'-,--precision-integer--+----------------------------------------------+--'
'-,--scale-integer--+-----------------------+--'
'-,--decimal-character--'
>--------------------------------------------------------------><
|
The DECIMAL function returns a packed decimal representation of:
The result of the function is a decimal number with precision of p and scale of s, where p and s are the second and third arguments. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Numeric to Decimal
The default for precision-integer depends on the data type of the numeric-expression:
The result is the same number that would occur if the first argument were assigned to a decimal column or variable with a precision of p and a scale of s. An error occurs if the number of significant decimal digits required to represent the whole part of the number is greater than p-s.
Character to Decimal
The result is the same number that would result from CAST(character-expression AS DECIMAL(p,s)). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming a floating-point, integer, or decimal constant. Digits are truncated from the end if the number of digits to the right of the decimal character is greater than the scale s. An error occurs if the number of significant digits to the left of the decimal character (the whole part of the number) in character-expression is greater than p-s. The default decimal character is not valid in the substring if the decimal-character argument is specified.
SELECT EMPNO, DECIMAL(EDLEVEL,5,2) FROM EMPLOYEE
SELECT PRSTDATE + DECIMAL(:PERIOD,8) FROM PROJECT
UPDATE STAFF SET SALARY = DECIMAL(:newsalary, 9, 2, ',') WHERE ID = :empidThe value of SALARY becomes 21400.50.
>>-DEGREES--(--expression--)----------------------------------->< |
The DEGREES function returns the number of degrees in an angle.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable RAD is a decimal (4,3) host variable with a value of 3.142.
DEGREES(:RAD)
Returns the approximate value 180.0.
>>-DIGITS--(--expression--)------------------------------------>< |
The DIGITS function returns a character-string representation of the absolute value of a number.
The argument must be an integer or decimal value.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is a fixed-length character string representing the absolute value of the argument without regard to its scale. The result does not include a sign or a decimal point. Instead, it consists exclusively of digits, including, if necessary, leading zeros to fill out the string. The length of the string is:
The CCSID of the character string is the default SBCS CCSID at the current server.
SELECT DISTINCT SUBSTR(DIGITS(INTCOL),1,4) FROM TABLEX
DIGITS(COLUMNX)returns the value '000628'.
The result is a string of length six (the precision of the column) with leading zeros padding the string out to this length. Neither sign nor decimal point appear in the result.
>>-DLCOMMENT--(--DataLink-expression--)------------------------>< |
The DLCOMMENT function returns the comment value, if it exists, from a DataLink value.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is VARCHAR(254).
The CCSID of the character string is the same as that of DataLink-expression.
stmtvar = "SELECT DATE_OF_GOAL, DESCRIPTION, DLCOMMENT(ARTICLES)
FROM HOCKEY_GOALS
WHERE BY_PLAYER = 'Maurice Richard' OR BY_PLAYER = 'Henri Richard' ";
EXEC SQL PREPARE HOCKEY_STMT FROM :stmtvar;
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLCOMMENT(COLA)will return the value:
A comment
>>-DLLINKTYPE--(--DataLink-expression--)----------------------->< |
The DLLINKTYPE function returns the link type value from a DataLink value.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is VARCHAR(4).
The CCSID of the character string is the same as that of DataLink-expression.
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLLINKTYPE(COLA)will return the value:
URL
>>-DLURLCOMPLETE--(--DataLink-expression--)-------------------->< |
The DLURLCOMPLETE function returns the complete URL value from a DataLink value with a link type of URL. The value is the same as what would be returned by the concatenation of DLURLSCHEME with '://', then DLURLSERVER, then '/' and then DLURLPATH. If the DataLink has an attribute of FILE LINK CONTROL and READ PERMISSION DB, the value includes a file access token.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is a varying-length string. The length attribute depends on the attributes of the DataLink:
If the DataLink value only includes the comment, the result returned is a zero length string.
The CCSID of the character string is the same as that of DataLink-expression.
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLURLCOMPLETE(COLA)will return the value:
HTTP://DLFS.ALMADEN.IBM.COM/x/y/****************;a.b
(where **************** represents the access token)
>>-DLURLPATH--(--DataLink-expression--)------------------------>< |
The DLURLPATH function returns the path and file name necessary to access a file within a given server from a DataLink value with a linktype of URL. When appropriate, the value includes a file access token.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is a varying-length string. The length attribute depends on the attributes of the DataLink:
If the DataLink value only includes the comment, the result returned is a zero length string.
The CCSID of the character string is the same as that of DataLink-expression.
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLURLPATH(COLA)will return the value:
/x/y/****************;a.b
(where **************** represents the access token)
>>-DLURLPATHONLY--(--DataLink-expression--)-------------------->< |
The DLURLPATHONLY function returns the path and file name necessary to access a file within a given server from a DataLink value with a linktype of URL. The value returned NEVER includes a file access token.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is a varying-length string with a length attribute of that is equal to the length attribute of the argument.
If the DataLink value only includes the comment, the result returned is a zero length string.
The CCSID of the character string is the same as that of DataLink-expression.
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLURLPATHONLY(COLA)will return the value:
/x/y/a.b
>>-DLURLSCHEME--(--DataLink-expression--)---------------------->< |
The DLURLSCHEME function returns the scheme from a DataLink value with a linktype of URL. The value will always be in upper case.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is VARCHAR(20).
If the DataLink value only includes the comment, the result returned is a zero length string.
The CCSID of the character string is the same as that of DataLink-expression.
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLURLSCHEME(COLA)will return the value:
HTTP
>>-DLURLSERVER--(--DataLink-expression--)---------------------->< |
The DLURLSERVER function returns the file server from a DataLink value with a linktype of URL. The value will always be in upper case.
The argument must be an expression that results in a value with data type of DataLink.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is a varying-length string with a length attribute of that is equal to the length attribute of the argument.
If the DataLink value only includes the comment, the result returned is a zero length string.
The CCSID of the character string is the same as that of DataLink-expression.
DLVALUE('http://dlfs.almaden.ibm.com/x/y/a.b','URL','A comment')
then the following function operating on that value:
DLURLSERVER(COLA)will return the value:
DLFS.ALMADEN.IBM.COM
>>-DLVALUE--(--data-location--+---------------------------------------------+---)-->
'-,--linktype-string--+--------------------+--'
'-,--comment-string--'
>--------------------------------------------------------------><
|
The DLVALUE function returns a DataLink value. When the function is on the right hand side of a SET clause in an UPDATE statement or is in a VALUES clause in an INSERT statement, it usually also creates a link to a file. However, if only a comment is specified (in which case the data-location is a zero-length string), the DataLink value is created with empty linkage attributes so there is no file link.
The comment-string cannot be the null value. If a comment-string is not specified, the comment-string is the empty string.
If any of the arguments can be null, the result can be null; if any of the arguments is null, the result is the null value.
The result of the function is a DataLink value.
The CCSID of the DataLink is the same as that of data-location except in the following cases:
| CCSID of data-location | CCSID of comment-string | Result CCSID |
|---|---|---|
| 65535 | 65535 | Job Default CCSID |
| 65535 | non-65535 | comment-string CCSID (unless the CCSID is 290, 930, 5026, 905, 1026, or 13488 where the CCSID will then be further modified as described in the following rows.) |
| 290 | any | 4396 |
| 930 or 5026 | any | 939 |
| 905 or 1026 | any | 500 |
| 13488 | any | 500 |
When defining a DataLink value using this function, consider the maximum length of the target of the value. For example, if a column is defined as DataLink(200), then the maximum length of the data-location plus the comment is 200 bytes.
INSERT INTO HOCKEY_GOALS
VALUES('Maurice Richard',
'Montreal canadian',
'?',
'Boston Bruins,
'1952-04-24',
'Winning goal in game 7 of Stanley Cup final',
DLVALUE(:url_article),
DLVALUE(:url_snapshot, 'URL', :url_snapshot_comment),
DLVALUE('', 'URL', :url_movie_comment) )
>>-+-DOUBLE_PRECISION-+--(--+-numeric-expression---+---)------->< '-DOUBLE-----------' '-character-expression-' |
The DOUBLE_PRECISION and DOUBLE functions return a floating-point representation of:
If the argument is a numeric-expression, the result is the same number that would occur if the argument were assigned to a double-precision floating-point column or variable.
If the argument is a character-expression, the result is the same number that would result from CAST( character-expression AS DOUBLE PRECISION). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming an floating-point, integer, or decimal constant.
The result of the function is a double-precision floating-point number. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the EMPLOYEE table, find the ratio of salary to commission for employees whose commission is not zero. The columns involved (SALARY and COMM) have DECIMAL data types. To eliminate the possibility of out-of-range results, DOUBLE_PRECISION is applied to SALARY so that the division is carried out in floating point:
SELECT EMPNO, DOUBLE_PRECISION(SALARY)/COMM FROM EMPLOYEE WHERE COMM > 0
>>-EXP--(--expression--)--------------------------------------->< |
The EXP function returns a value that is the base of the natural logarithm (e) raised to a power specified by the argument. The EXP and LN functions are inverse operations.
The argument must be a number.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable E is a decimal (10,9) host variable with a value of 3.453789832.
EXP(:E)
Returns the approximate value 31.62.
>>-FLOAT--(--+-numeric-expression---+---)----------------------><
'-character-expression-'
|
The FLOAT function is identical to the DOUBLE_PRECISION and DOUBLE scalar functions. For more information, see "DOUBLE_PRECISION or DOUBLE".
>>-FLOOR--(--expression--)------------------------------------->< |
The FLOOR function is returns the largest integer value less than or equal to the argument.
The argument must be a number.
The data type and length attribute of the result are the same as the data type and length attribute of the argument value. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Use the FLOOR function to truncate any digits to the right of the decimal point.
SELECT FLOOR(SALARY) FROM CORPDATA.EMPLOYEE
.-,-------------.
V |
>>-HASH--(-------expression---+--)-----------------------------><
|
The HASH function returns the partition number of a set of values. Also see the PARTITION function. For more information about partition numbers, see the DB2 Multisystem book.
The arguments cannot be datetime, floating-point, or DataLink values. The arguments must not be parameter markers.
The result of the function is a large integer with a value between 0 and 1023.
If any of the arguments are null, the result is zero. The result cannot be null.
Use the HASH function to determine what the partitions would be if the partitioning key was composed of EMPNO and LASTNAME. This query returns the partition number for every row in EMPLOYEE.
SELECT HASH(EMPNO, LASTNAME) FROM CORPDATA.EMPLOYEE
>>-HEX--(--expression--)--------------------------------------->< |
The HEX function returns a hexadecimal representation of a value.
The argument can be any value.
The result of the function is a character string. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is a string of hexadecimal digits, the first two digits represent the first byte of the argument, the next two digits represent the second byte of the argument, and so forth. If the argument is a date or time value, the result is the hexadecimal representation of the internal form of the argument. This hexadecimal representation for DATE, TIMESTAMP, and NUMERIC data types is different from other database products because the internal form for these data types is different.
If the argument is not a graphic string, the length of the result is twice the length of the argument. If the argument is a graphic string, the length of the result is four times the length of the argument.
If the argument is a varying-length string, the result is a varying-length string. Otherwise, the result is a fixed-length string. The length attribute of the result is twice the storage length attribute of the argument. For information on the storage length attribute see "CREATE TABLE".
The length attribute of the result cannot be greater than 32766 for fixed-length results or greater than 32740 for varying-length results. The CCSID of the string is the default SBCS CCSID at the current server.
Use the HEX function to return a hexadecimal representation of the education level for each employee.
SELECT FIRSTNME, MIDINIT, LASTNAME, HEX(EDLEVEL) FROM EMPLOYEE
>>-HOUR--(--expression--)-------------------------------------->< |
The HOUR function returns the hour part of a value.
The argument must be a time, timestamp, time duration, or timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the hour part of the value, which is an integer between 0 and 24.
The result is the hour part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Using the CL_SCHED sample table, select all the classes that start in the afternoon.
SELECT * FROM CL_SCHED WHERE HOUR(STARTING) BETWEEN 12 AND 17
>>-IFNULL--(--expression--,--expression--)--------------------->< |
The IFNULL function is identical to the COALESCE scalar function with two arguments. For more information, see "COALESCE".
When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is, null), then return a value of zero.
SELECT EMPNO, IFNULL(SALARY,0) FROM EMPLOYEE
>>-+-INTEGER-+--(--+-numeric-expression---+---)---------------->< '-INT-----' '-character-expression-' |
The INTEGER function returns an integer representation of:
If the argument is a numeric-expression, the result is the same number that would occur if the argument were assigned to a large integer column or variable. If the whole part of the argument is not within the range of integers, an error occurs. The fractional part of the argument is truncated.
If the argument is a character-expression, the result is the same number that would result from CAST( character-expression AS INTEGER). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming a floating-point, integer, or decimal constant. If the whole part of the argument is not within the range of integers, an error occurs. Any fractional part of the argument is truncated.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the EMPLOYEE table, select a list containing salary (SALARY) divided by education level (EDLEVEL). Truncate any decimal in the calculation. The list should also contain the values used in the calculation and the employee number (EMPNO).
SELECT INTEGER(SALARY / EDLEVEL), SALARY, EDLEVEL, EMPNO FROM EMPLOYEE
.------------------.
V |
>>-LAND--(--expression-----,--expression---+---)---------------><
|
The LAND function returns a string that is the logical 'AND' of the argument strings. This function takes the first argument string, does an AND comparison with the next string, and then continues to do AND comparisons with each successive argument using the previous result. If an argument is encountered that is shorter than the previous result, it is padded with blanks.
The arguments must be character strings but cannot be LOBs. The arguments cannot be mixed data character strings or graphic strings. There must be two or more arguments. Arguments other than the first argument may be parameter markers.
The arguments are converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows:
If an argument can be null, the result can be null; if an argument is null, the result is the null value.
Assume the host variable L1 is a character(2) host variable with a value of X'A1B1', host variable L2 is a character(3) host variable with a value of X'F0F040', and host variable L3 is a character(4) host variable with a value of X'A1B10040'.
LAND(:L1,:L2,:L3)
Returns the value X'A0B00000'.
LAND(:L3,:L2,:L1)
Returns the value X'A0B00040'. In this case, the shorter arguments are padded with blanks (X'40'), so the logical AND result differs from the first example.
>>-LEFT--(--string--,--length--)------------------------------->< |
The LEFT function returns the leftmost specified number of characters from the string. 23
If string is a character string, the result is a character string, and each character is one byte. If string is a graphic string, the result is a graphic string, and each character is a DBCS or UCS-2 character.
If any argument of the LEFT function can be null, the result can be null; if any argument is null, the result is the null value.
The CCSID of the result is the same as that of string.
The LEFT function accepts mixed data. However, because LEFT operates on a strict byte-count basis, the result is not necessarily a properly formed mixed data character string.
The string is effectively padded on the right with the necessary number of blank characters so that the specified substring of string always exists.
If length is specified by an integer constant, the result is a fixed-length string. In all other cases, the result is a varying-length string with a length attribute that is the same as the length attribute of string.
Assume the host variable NAME (varchar(50)) has a value of 'KATIE AUSTIN' and the host variable FIRSTNAME_LEN (int) has a value of 5.
LEFT(:NAME, :FIRSTNAME_LEN)
Returns the value 'KATIE'
>>-LENGTH--(--expression--)------------------------------------>< |
The LENGTH function returns the length of a value. See CHARACTER_LENGTH or CHAR_LENGTH for a similar function.
The argument can be any value.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is the length of the argument. The length of strings includes blanks. The length of a varying-length string is the actual length, not the maximum length.
The length of a graphic string is the number of characters. The length of all other values is the number of bytes used to represent the value:
Numbers:
Character strings:
Graphic strings:
Datetime values:
DataLink values:
LENGTH(:ADDRESS)Returns the value 18.
LENGTH(START_DATE)Returns the value 4.
LENGTH(CHAR(START_DATE, EUR))
Returns the value 10.
>>-LN--(--expression--)---------------------------------------->< |
The LN function returns the natural logarithm of a number. The LN and EXP functions are inverse operations.
The argument must be a number.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable NATLOG is a decimal (4,2) host variable with a value of 31.62.
LN(:NATLOG)
Returns the approximate value 3.45.
>>-LNOT--(--expression--)-------------------------------------->< |
The LNOT function returns a string that is the logical NOT of the argument string.
The argument must be a character string but cannot be a LOB. The argument cannot be a MIXED character string or a graphic string.
The data type and length attribute of the result is the same as the data type and length attribute of the argument value. If the argument is a varying-length string, the actual length of the result is the same as the actual length of the argument value. The CCSID of the result is 65535. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable L1 is a character(2) host variable with a value of X'F0F0'.
LNOT(:L1)
Returns the value X'0F0F'.
>>-LOCATE--(--search-string--,--source-string--+-----------+---)-->
'-,--start--'
>--------------------------------------------------------------><
|
The LOCATE function returns the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string.
The result of the function is a large integer. If any of the arguments can be null, the result can be null; if any of the arguments is null, the result is the null value.
If the CCSID of the search-string is different than the CCSID of the source-string, it is converted to the CCSID of the source-string.
If start is not specified, the function is equivalent to:
POSSTR( source-string , search-string )
If start is specified, the function is equivalent to:
POSSTR( SUBSTR(source-string,start) , search-string )
For more information, see "POSITION or POSSTR".
SELECT RECEIVED, SUBJECT, LOCATE('GOOD', NOTE_TEXT)
FROM IN_TRAY
WHERE LOCATE('GOOD', NOTE_TEXT) <> 0
>>-+-LOG---+--(--expression--)--------------------------------->< '-LOG10-' |
The LOG and LOG10 functions return the common logarithm (base 10) of a number. The LOG and ANTILOG functions are inverse operations.
The argument must be a number.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
LOG10 should be used instead of LOG because some database managers and applications implement LOG as the natural logarithm of a number instead of the common logarithm of a number.
Assume the host variable L is a decimal (4,2) host variable with a value of 31.62.
LOG(:L)
Returns the approximate value 1.49.
.------------------.
V |
>>-LOR--(--expression-----,--expression---+---)----------------><
|
The LOR function returns a string that is the logical OR of the argument strings. This function takes the first argument string, does an OR comparison with the next string, and then continues to do OR comparisons for each successive argument using the previous result. If an argument is encountered that is shorter than the previous result, it is padded with blanks.
The arguments must be character strings but cannot be LOBs. The arguments cannot be mixed data character strings or graphic strings. There must be two or more arguments. Arguments other than the first argument may be parameter markers.
The arguments are converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows:
If an argument can be null, the result can be null; if an argument is null, the result is the null value.
Assume the host variable L1 is a character(2) host variable with a value of X'0101', host variable L2 is a character(3) host variable with a value of X'F0F000', and host variable L3 is a character(4) host variable with a value of X'0000000F'.
LOR(:L1,:L2,:L3)
Returns the value X'F1F1000F'.
LOR(:L3,:L2,:L1)
Returns the value X'F1F1404F'. In this case, the shorter arguments are padded with blanks (X'40'), so the logical OR result differs from the first example.
>>-+-LCASE-+--(--expression--)--------------------------------->< '-LOWER-' |
The LCASE or LOWER function returns a string in which all the characters have been converted to lowercase characters, based on the CCSID of the argument. Only SBCS and UCS-2 graphic characters are converted. Refer to the National Language Support Reference Volume 2, SE09-8002, for a description of the monocasing tables that are used for this translation.
The argument must be an expression whose value is a character string or a graphic string.
The result of the function has the same data type, length attribute, actual length, and CCSID as the argument. If the argument can be null, the result can be null. If the argument is null, the result is the null value.
LCASE(:NAME)
The result is the value 'christine smith'.
>>-LTRIM--(--expression--)------------------------------------->< |
The LTRIM function removes blanks or hexadecimal zeros from the beginning of a string expression. 24
The argument must be a string expression.
The data type of the result depends on the data type of
expression:
| Data type of expression | Data type of the Result |
|---|---|
| CHAR or VARCHAR | VARCHAR |
| GRAPHIC or VARGRAPHIC | VARGRAPHIC |
| BLOB | BLOB |
| CLOB | CLOB |
| DBCLOB | DBCLOB |
The length attribute of the result is the same as the length attribute of expression. The actual length of the result is the length of the expression minus the number of bytes removed. If all characters are removed, the result is an empty string.
The CCSID of the result is the same as that of the string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Assume the host variable HELLO of type CHAR(9) has a value of ' Hello'.
LTRIM(:HELLO)
Results in: 'Hello'.
.------------------.
V |
>>-MAX--(--expression-----,--expression---+---)----------------><
|
The MAX scalar function returns the maximum value in a set of values.
The arguments must be compatible. Character-string arguments are compatible with datetime values, but are not compatible with graphic strings. All but the first argument may be parameter markers. There must be two or more arguments. The arguments cannot be DataLink values.
The result of the function is the largest argument value. The result can be null if at least one argument can be null; the result is the null value if one of the arguments is null. The selected arguments are converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows:
If a sort sequence other than *HEX is in effect when the statement is executed and SBCS, UCS-2, or mixed data is involved, the weighted values of the strings are compared instead of the actual values. The weighted values are based on the sort sequence.
MAX(:M1,:M2,:M3)
Returns the value 6.25.
MAX(:M1,:M2,:M3)
Returns the value 'AA A'.
>>-MICROSECOND--(--expression--)------------------------------->< |
The MICROSECOND function returns the microsecond part of a value.
The argument must be a timestamp, a string representation of a timestamp, or a timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the microsecond part of the value, which is an integer between 0 and 999999.
The result is the microsecond part of the value, which is an integer between -999999 and 999999. A nonzero result has the same sign as the argument.
Assume a table TABLEA contains two columns, TS1 and TS2, of type TIMESTAMP. Select all rows in which the microseconds portion of TS1 is not zero and the seconds portion of TS1 and TS2 are identical.
SELECT * FROM TABLEA WHERE MICROSECOND(TS1) <> 0 AND SECOND(TS1) = SECOND(TS2)
.------------------.
V |
>>-MIN--(--expression-----,--expression---+---)----------------><
|
The MIN scalar function returns the minimum value in a set of values.
The arguments must be compatible. Character-string arguments are compatible with datetime values, but are not compatible with graphic strings. All but the first argument may be parameter markers. There must be two or more arguments. The arguments cannot be DataLink values.
The result of the function is the smallest argument value. The result can be null if at least one argument can be null; the result is the null value if one of the arguments is null. The selected arguments are converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows:
If a sort sequence other than *HEX is in effect when the statement is executed and SBCS, UCS-2, or mixed data is involved, the weighted values of the strings are compared instead of the actual values. The weighted values are based on the sort sequence.
MIN(:M1,:M2,:M3)
Returns the value 4.50.
MIN(:M1,:M2,:M3)
Returns the value 'AA '.
>>-MINUTE--(--expression--)------------------------------------>< |
The MINUTE function returns the minute part of a value.
The argument must be a time, timestamp, time duration, or timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the minute part of the value, which is an integer between 0 and 59.
The result is the minute part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
Using the CL_SCHED sample table, select all classes with a duration less than 50 minutes.
SELECT * FROM CL_SCHED WHERE HOUR(ENDING - STARTING) = 0 AND MINUTE(ENDING - STARTING) < 50
>>-MOD--(--expression--,--expression----)---------------------->< |
The MOD function divides the first argument by the second argument and returns the remainder.
The formula used to calculate the remainder is:
MOD(x,y) = x - (x/y) * y
where x/y is the truncated integer result of the division.
The arguments must be numbers. The second argument cannot be zero.
If an argument can be null, the result can be null; if an argument is null, the result is the null value.
The attributes of the result are determined as follows:
The operation is performed in floating point; the operands having been first converted to double-precision floating-point numbers, if necessary.
An operation involving a floating-point number and an integer is performed with a temporary copy of the integer that has been converted to double-precision floating point. An operation involving a floating-point number and a decimal number is performed with a temporary copy of the decimal number that has been converted to double-precision floating point. The result of a floating-point operation must be within the range of floating-point numbers.
MOD(:M1,:M2)
Returns the value 1.
MOD(:M1,:M2)
Returns the value 0.6.
MOD(:M1,:M2)
Returns the value 0.60.
MOD(:M1,:M2)
Returns the value 1.50.
>>-MONTH--(--expression--)------------------------------------->< |
The MONTH function returns the month part of a value.
The argument must be a date, timestamp, date duration, or timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the month part of the value, which is an integer between 1 and 12.
The result is the month part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
SELECT * FROM EMPLOYEE WHERE MONTH(BIRTHDATE) = 12
>>-NODENAME--(--table-designator--)---------------------------->< |
The NODENAME function returns the relational database name of where a row is located. If the argument identifies a non-distributed table, the value of the CURRENT SERVER special register is returned. For more information on nodes, see the DB2 Multisystem book.
The argument is a table designator of the subselect. For more information on table designators, see "Table Designators".
In SQL naming, the table name may be qualified. In system naming, the table name cannot be qualified.
If the argument identifies a view, the function returns the relational database name of its base table. If the argument identifies a view derived from more than one base table, the function returns the relational database name of the first table in the outer subselect of the view.
The argument must not identify a view whose outer subselect includes a column function, a GROUP BY clause, or a HAVING clause. If the subselect contains a GROUP BY or HAVING clause, the NODENAME function can only be specified in the WHERE clause or as an operand of a column function. If the argument is a correlation name, the correlation name must not identify a correlated reference.
The data type of the result is VARCHAR(18). The result cannot be null.
Join the EMPLOYEE and DEPARTMENT tables, select the employee number (EMPNO) and determine the node from which each row involved in the join originated.
SELECT EMPNO, NODENAME(X), NODENAME(Y) FROM CORPDATA.EMPLOYEE X, CORPDATA.DEPARTMENT Y WHERE X.DEPTNO=Y.DEPTNO
>>-NODENUMBER--(--table-designator--)-------------------------->< |
The NODENUMBER function returns the node number of a row. If the argument identifies a non-distributed table, the value 0 is returned. For more information on nodes and node numbers, see the DB2 Multisystem book.
The argument is a table designator of the subselect. For more information on table designators, see "Table Designators".
In SQL naming, the table name may be qualified. In system naming, the table name cannot be qualified.
If the argument identifies a view, the function returns the node number of its base table. If the argument identifies a view derived from more than one base table, the function returns the node number of the first table in the outer subselect of the view.
The argument must not identify a view whose outer subselect includes a column function, a GROUP BY clause, or a HAVING clause. If the subselect contains a GROUP BY or HAVING clause, the NODENUMBER function can only be specified in the WHERE clause or as an operand of a column function. If the argument is a correlation name, the correlation name must not identify a correlated reference.
The data type of the result is a large integer. The result cannot be null.
Determine the node number and employee name for each row in the CORPDATA.EMPLOYEE table. If this is a distributed table, the number of the node where the row exists is returned.
SELECT NODENUMBER(CORPDATA.EMPLOYEE), LASTNAME
FROM CORPDATA.EMPLOYEE
>>-NOW--(--)--------------------------------------------------->< |
The NOW function returns a timestamp based on a reading of the time-of-day clock when the SQL statement is executed at the current server. The value returned by the NOW function is the same as the value returned by the CURRENT TIMESTAMP special register. If this function is used more than once within a single SQL statement, or used with the CURDATE or CURTIME scalar functions or the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special registers within a single statement, all values are based on a single clock reading.
The data type of the result is a timestamp.
Return the current timestamp based on the time-of-day clock.
NOW()
>>-NULLIF---(--expression--,--expression--)-------------------->< |
The NULLIF function returns a null value if the arguments are equal, otherwise it returns the value of the first argument.
The arguments must be compatible. Character-string arguments are compatible with datetime values. The arguments cannot be DataLink values.
The attributes of the result are the attributes of the first argument. The result can be null. The result is null if the first argument is null or if both arguments are equal.
The result of using NULLIF(e1,e2) is the same as using the expression
CASE WHEN e1=e2 THEN NULL ELSE e1 END
Note that when e1=e2 evaluates to unknown (because one or both arguments is NULL), CASE expressions consider this not true. Therefore, in this situation, NULLIF returns the value of the first operand, e1.
Assume host variables PROFIT, CASH, and LOSSES have DECIMAL data types with the values 4500.00, 500.00, and 5000.00 respectively:
NULLIF (:PROFIT + :CASH, :LOSSES )
Returns the null value.
>>-PARTITION--(--table-designator--)--------------------------->< |
The PARTITION function returns the partition number of a row obtained by applying the hashing function on the partitioning key value of the row. Also see the HASH function. If the argument identifies a non-distributed table, the value 0 is returned. For more information on partition numbers and partitioning keys, see the DB2 Multisystem book.
The argument is a table designator of the subselect. For more information on table designators, see "Table Designators".
In SQL naming, the table name may be qualified. In system naming, the table name cannot be qualified.
If the argument identifies an SQL view, the function returns the partition number of its base table. If the argument identifies an SQL View derived from more than one base table, the function returns the partition number of the first table in the outer subselect of the view.
The argument must not identify a view whose outer subselect includes a column function, a GROUP BY clause, or a HAVING clause. If the subselect contains a GROUP BY or HAVING clause, the PARTITION function can only be specified in the WHERE clause or as an operand of a column function. If the argument is a correlation name, the correlation name must not identify a correlated reference.
The data type of the result is a large integer with a value between 0 and 1023. The result cannot be null.
Select the employee number (EMPNO) from the EMPLOYEE table for all rows where the partition number is equal to 100.
SELECT EMPNO FROM CORPDATA.EMPLOYEE WHERE PARTITION(CORPDATA.EMPLOYEE) = 100
>>-+-POSITION--(--search-string--IN--source-string--)--+------->< '-POSSTR--(--source-string--,--search-string--)-----' |
The POSITION and POSSTR functions return the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string). If the search-string is not found and neither argument is null, the result is zero. If the search-string is found, the result is a number from 1 to the actual length of the source-string. See the related function, LOCATE.
The result of the function is a large integer. If either of the arguments can be null, the result can be null. If either of the arguments is null, the result is the null value.
If either argument is a binary string, both arguments must be binary strings.
If the CCSID of the search-string is different than the CCSID of the source-string, it is converted to the CCSID of the source-string.
The POSITION function operates on a character basis. The POSSTR function operates on a strict byte-count basis. It is recommended that if either the search-string or source-string contains mixed data, POSITION should be used instead of POSSTR. Because POSSTR operates on a strict byte-count basis, if the search-string or source-string contains mixed data, the search-string will only be found if any shift-in and shift-out characters are also found in the source-string in exactly the same positions. Because POSITION operates on a character-string basis, any shift-in and shift-out characters are not required to be in exactly the same position and their only significance is to indicate which characters are SBCS and which characters are DBCS.
If the search-string has a length of zero, the result returned by the function is 1. Otherwise:
SELECT RECEIVED, SUBJECT, POSSTR(NOTE_TEXT, 'GOOD') FROM IN_TRAY WHERE POSSTR(NOTE_TEXT, 'GOOD') <> 0
>>-POWER--(--expression--,--expression--)---------------------->< |
The POWER function returns the result of raising the first argument to the power of the second argument. 26
Both arguments must be numbers.
The result of the function is a double-precision floating-point number. If an argument can be null, the result can be null; if an argument is null, the result is the null value.
Assume the host variable HPOWER is an integer with value 3.
POWER(2,:HPOWER)
Returns the value 8.
>>-QUARTER--(--expression--)----------------------------------->< |
The QUARTER function returns an integer between 1 and 4 which represents the quarter of the year in which the date resides. For example, any dates in January, February, or March will return the integer 1.
The argument must be a date or a timestamp.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the PROJECT table, set the host variable QUART (int) to the quarter in which project 'PL2100' ended (PRENDATE).
SELECT QUARTER(PRENDATE)
INTO :QUART
FROM PROJECT
WHERE PROJNO = 'PL2100'
Results in QUART being set to 3 when using the sample table.
>>-REAL--(--+-numeric-expression---+---)-----------------------><
'-character-expression-'
|
The REAL function returns a single-precision floating-point representation of:
If the argument is a numeric-expression, the result is the same number that would occur if the argument were assigned to a single-precision floating-point column or variable.
If the argument is a character-expression, the result is the same number that would result from CAST( character-expression AS REAL). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming an floating-point, integer, or decimal constant.
The result of the function is a single-precision floating-point number. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the EMPLOYEE table, find the ratio of salary to commission for employees whose commission is not zero. The columns involved (SALARY and COMM) have DECIMAL data types. To eliminate the possibility of out-of-range results, REAL is applied to SALARY so that the division is carried out in floating point:
SELECT EMPNO, REAL(SALARY)/COMM FROM EMPLOYEE WHERE COMM > 0
>>-ROUND--(--expression1--,--expression2----)------------------>< |
The ROUND function returns expression1 rounded to expression2 places to the right of the decimal point if expression2 is positive or to the left of the decimal point if expression2 is zero or negative.
If expression2 is positive, a value of 5 is rounded to the next higher positive number. For example, ROUND(3.5,0) = 4. If the argument is negative, a value of 5 is rounded to the next lower negative number. For example, ROUND(-3.5,0) = -4.
If expression2 is negative, expression1 is rounded to the absolute value of expression2+1 number of places to the left of the decimal point.
If the absolute value of expression2 is larger than the number of digits to the left of the decimal point, the result is 0. For example, ROUND(748.58,-4) = 0.
The data type and length attribute of the result are the same as the data type and length attribute of the first argument, except that:
If either argument can be null, the result can be null. If either argument is null, the result is the null value.
SELECT ROUND(873.726, 2),
ROUND(873.726, 1),
ROUND(873.726, 0),
ROUND(873.726, -1),
ROUND(873.726, -2),
ROUND(873.726, -3),
ROUND(873.726, -4)
FROM TABLEX
This example returns:
0873.730 0873.700 0874.000 0870.000 0900.000 1000.000 0000.000
respectively.
SELECT ROUND( 3.5, 0),
ROUND( 3.1, 0),
ROUND(-3.1, 0),
ROUND(-3.5, 0)
FROM TABLEX
This example returns:
4.0 3.0 -3.0 -4.0
respectively.
>>-RRN--(--table-designator--)--------------------------------->< |
The RRN function returns the relative record number of a row.
The argument is a table designator of the subselect. For more information on table designators, see "Table Designators".
In SQL naming, the table name may be qualified. In system naming, the table name can not be qualified.
If the argument identifies a view, the function returns the relative record number of its base table. If the argument identifies a view derived from more than one base table, the function returns the relative record number of the first table in the outer subselect of the view.
If the argument identifies a distributed table, the function returns the relative record number of the row on the node where the row is located. This means that RRN will not be unique for each row of a distributed table.
The argument must not identify a view whose outer subselect includes a column function, a GROUP BY clause, or a HAVING clause. The RRN function cannot be specified in a SELECT clause if the subselect contains a column function, a GROUP BY clause, or a HAVING clause. If the argument is a correlation name, the correlation name must not identify a correlated reference.
The data type of the result is a decimal with precision 15 and scale 0. The result can be null. The RRN value for a row that is from a table on the right side for left outer and exception joins will return 0 for a row that does not match.
SELECT RRN(CORPDATA.EMPLOYEE), LASTNAME FROM CORPDATA.EMPLOYEE WHERE DEPTNO = 20
Returns the relative record number and employee name from table EMPLOYEE for those employees in department 20.
>>-RTRIM--(--expression--)------------------------------------->< |
The RTRIM function removes blanks or hexadecimal zeroes from the end of a string expression. 27
The argument must be a string expression.
The data type of the result depends on the data type of
expression:
| Data type of expression | Data type of the Result |
|---|---|
| CHAR or VARCHAR | VARCHAR |
| GRAPHIC or VARGRAPHIC | VARGRAPHIC |
| BLOB | BLOB |
| CLOB | CLOB |
| DBCLOB | DBCLOB |
The length attribute of the result is the same as the length attribute of expression. The actual length of the result is the length of the expression minus the number of bytes removed. If all characters are removed, the result is an empty string.
The CCSID of the result is the same as that of the string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Assume the host variable HELLO of type CHAR(9) has a value of 'Hello '.
RTRIM(:HELLO)
Results in: 'Hello'.
>>-SECOND--(--expression--)------------------------------------>< |
The SECOND function returns the seconds part of a value.
The argument must be a time, timestamp, time duration, or timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the seconds part of the value, which is an integer between 0 and 59.
The result is the seconds part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.
SECOND(:TIME_DUR)
Returns the value 45.
SECOND(RECEIVED)
Returns the value 30.
>>-SIGN--(--numeric-expression--)------------------------------>< |
The SIGN function returns an indicator of the sign of expression. The returned value is:
The argument is an expression that returns a value of any built-in numeric data type.
The result has the same data type and length attribute as the argument, except that:
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume that host variable PROFIT is a large integer with a value of 50000.
SELECT SIGN(:PROFIT) FROM CORPDATA.EMPLOYEE
This example returns the value 1.
>>-SIN--(--expression--)--------------------------------------->< |
The SIN function returns the sine of a number. The SIN and ASIN functions are inverse operations.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable SINE is a decimal (2,1) host variable with a value of 1.5.
SIN(:SINE)
Returns the approximate value 0.99.
>>-SINH--(--expression--)-------------------------------------->< |
The SINH function returns the hyperbolic sine of a number.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable HSINE is a decimal (2,1) host variable with a value of 1.5.
SINH(:HSINE)
Returns the approximate value 2.12.
>>-SMALLINT--(--+-numeric-expression---+---)-------------------><
'-character-expression-'
|
The SMALLINT function returns a small integer representation of
If the argument is a numeric-expression, the result is the same number that would occur if the argument were assigned to a small integer column or variable. If the whole part of the argument is not within the range of small integers, an error occurs. The fractional part of the argument is truncated.
If the argument is a character-expression, the result is the same number that would result from CAST( character-expression AS SMALLINT). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming a floating-point, integer, or decimal constant. If the whole part of the argument is not within the range of small integers, an error occurs. Any fractional part of the argument is truncated.
The result of the function is a small integer. If the argument can be null, the result can be null. If the argument is null, the result is the null value.
Using the EMPLOYEE table, select a list containing salary (SALARY) divided by education level (EDLEVEL). Truncate any decimal in the calculation. The list should also contain the values used in the calculation and the employee number (EMPNO).
SELECT SMALLINT(SALARY / EDLEVEL), SALARY, EDLEVEL, EMPNO FROM EMPLOYEE
>>-SQRT--(--expression--)-------------------------------------->< |
The SQRT function returns the square root of a number.
The argument must be a number whose value is a positive numeric value.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable SQUARE is a decimal (2,1) host variable with a value of 9.0.
SQRT(:SQUARE)
Returns the approximate value 3.00.
>>-STRIP--(--expression--+----------------------------------------------+---)-->
'--+-,--BOTH------+---+---------------------+--'
+-,--B---------+ '-,--strip-character--'
+-,--LEADING---+
+-,--L---------+
+-,--TRAILING--+
'-,--T---------'
>--------------------------------------------------------------><
|
The STRIP function is identical to the TRIM scalar function. For more information, see "TRIM".
>>-+--+-SUBSTR----+---(--string--,--start--+------------+---)--+->
| '-SUBSTRING-' '-,--length--' |
'-SUBSTRING--(--string--FROM--start--+---------------+---)--'
'- FOR--length--'
>--------------------------------------------------------------><
|
The SUBSTR and SUBSTRING functions are used to obtain a substring of a string.
If string is a character string, the result is a character string. If string is a graphic string, the result is a graphic string. If string is a binary string, the result is a binary string. If string is a binary or character string, a character is a byte. If string is a graphic string, a character is a DBCS or UCS-2 character. If any argument of the SUBSTR function can be null, the result can be null; if any argument is null, the result is the null value. The CCSID of the result is the same as that of string.
A substring of string is zero or more contiguous bytes of string. The SUBSTR function accepts mixed data. However, because SUBSTR operates on a strict byte-count basis, the result is not necessarily a properly formed mixed data character string. The SUBSTRING function accepts mixed data. Because SUBSTRING operates on a character-count basis, the result is a properly formed mixed data character string.
If string is a varying-length string and length is explicitly specified, string is effectively padded on the right with the necessary number of blank characters so that the specified substring of string always exists.
If string is a fixed-length string, omission of length is an implicit specification of LENGTH(string) - start + 1, which is the number of characters (or bytes) from the start character (or byte) to the last character (or byte) of string. If string is a varying-length string, omission of length is an implicit specification of zero or LENGTH(string) - start + 1, whichever is greater. If the resulting length is zero, the result is the empty string.
The data type of the result depends on the data type of string
and whether the function is a SUBSTR or SUBSTRING:
| Data type of string | Data Type of the Result for SUBSTRING | Data Type of the Result for SUBSTR |
|---|---|---|
| CHAR or VARCHAR | VARCHAR | CHAR, if length is explicitly specified by an integer constant or if length is not explicitly specified, but string is a fixed-length string and start is an integer constant. VARCHAR, in all other cases. |
| GRAPHIC or VARGRAPHIC | VARGRAPHIC | GRAPHIC, if length is explicitly specified by an integer constant or if length is not explicitly specified, but string is a fixed-length string and start is an integer constant. VARGRAPHIC, in all other cases. |
| BLOB | BLOB | BLOB |
| CLOB | CLOB | CLOB |
| DBCLOB | DBCLOB | DBCLOB |
If the SUBSTRING function is specified, the length attribute of the result is equal to the length attribute of string.
If the SUBSTR function is specified and string is not a LOB, the length attribute of the result depends on length, start, and the attributes of string.
SUBSTR(:NAME, :SURNAME_POS)
Returns the value 'AUSTIN'
SUBSTR(:NAME, :SURNAME_POS, 1)
Returns the value 'A'.
SELECT * FROM PROJECT WHERE SUBSTR(PROJNAME,1,10) = 'OPERATION 'The space at the end of the constant is necessary to preclude initial words such as 'OPERATIONS'.
>>-TAN--(--expression--)--------------------------------------->< |
The TAN function returns the tangent of a number. The TAN and ATAN functions are inverse operations.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable TANGENT is a decimal (2,1) host variable with a value of 1.5.
TAN(:TANGENT)
Returns the approximate value 14.10.
>>-TANH--(--expression--)-------------------------------------->< |
The TANH function returns the hyperbolic tangent of a number. The TANH and ATANH functions are inverse operations.
The argument must be a number whose value is specified in radians.
The data type of the result is double-precision floating point. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Assume the host variable HTANGENT is a decimal (2,1) host variable with a value of 1.5.
TANH(:HTANGENT)
Returns the approximate value 0.90.
>>-TIME--(--expression--)-------------------------------------->< |
The TIME function returns a time from a value.
The argument must be a timestamp, a time, or a valid string representation of a time.
The result of the function is a time. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the time part of the timestamp.
The result is that time.
When a string representation of a time is SBCS data with a CCSID that is not the same as the default CCSID for SBCS data, that value is converted to adhere to the default CCSID for SBCS data before it is interpreted and converted to a time value.
When a string representation of a time is mixed data with a CCSID that is not the same as the default CCSID for mixed data, that value is converted to adhere to the default CCSID for mixed data before it is interpreted and converted to a time value.
Select all notes from the IN_TRAY sample table that were received at least one hour later in the day (any day) than the current time.
SELECT * FROM IN_TRAY WHERE TIME(RECEIVED) >= CURRENT TIME + 1 HOUR
>>-TIMESTAMP--(--expression--+----------------+---)------------><
'-,--expression--'
|
The TIMESTAMP function returns a timestamp from a value or a pair of values.
The rules for the arguments depend on whether the second argument is specified.
It must be a timestamp, a valid string representation of a timestamp, or a character string of length 14.
A character string of length 14 must be a string of digits that represents a valid date and time in the form yyyyxxddhhmmss, where yyyy is year, xx is month, dd is day, hh is hour, mm is minute, and ss is seconds.
The first argument must be a date or a valid string representation of a date; the second argument must be a time or a valid string representation of a time.
The result of the function is a timestamp. If either argument can be null, the result can be null; if either argument is null, the result is the null value.
The other rules depend on whether the second argument is specified:
The result is a timestamp with the date specified by the first argument and the time specified by the second argument. The microsecond part of the timestamp is zero.
The result is that timestamp.
The result is the timestamp represented by that character string. If the argument is a character string of length 14, the timestamp has a microsecond part of zero.
When a string representation of a timestamp is SBCS data with a CCSID that is not the same as the default CCSID for SBCS data, that value is converted to adhere to the default CCSID for SBCS data before it is interpreted and converted to a timestamp value.
When a string representation of a timestamp is mixed data with a CCSID that is not the same as the default CCSID for mixed data, that value is converted to adhere to the default CCSID for mixed data before it is interpreted and converted to a timestamp value.
TIMESTAMP(START_DATE, START_TIME)
Returns the value '1988-12-25-17.12.30.000000'.
>>-TRANSLATE---------------------------------------------------->
>-----(--expression--+-----------------------------------------------------------+---)->
'-,--to-string--+----------------------------------------+--'
'-,--from-string--+-------------------+--'
'-,--pad-character--'
>--------------------------------------------------------------><
The TRANSLATE function translates one or more characters of the first argument.
The first argument must be a character string or a UCS-2 graphic string. The data type and length attribute of the result are the same as the first argument.
The second argument is a character string constant of no more than 256 characters. Note that this is sometimes called the output translation table. If the length attribute of the to-string is less than the length attribute of the from-string, then the to-string is padded to the longer length using either the pad-character or a blank. If the length attribute of the to-string is greater than the length attribute of the from-string, the extra characters in to-string are ignored without warning.
The third argument is a character string constant of no more than 256 characters. Note that this is sometimes called the input translation table. If there are duplicate characters in from-string, the first one scanning from the left is used and no warning is issued. The default value for from-string is a string starting with the character X'00' and ending with the character X'FF' (decimal 255).
The fourth argument is a character constant of length 1 that is used to pad the to-string if it is shorter than the from-string. The default value for the pad-character is an SBCS space.
If the first argument is a UCS-2 graphic string, no other arguments may be specified.
If only the first argument is specified, the SBCS characters of the argument are translated to uppercase, based on the CCSID of the argument. If the first argument is UCS-2 graphic, the alphabetic UCS-2 characters are translated to uppercase. Refer to the National Language Support Reference Volume 2, SE09-8002, for a description of the monocasing tables that are used for this translation.
Otherwise, the result string is built character by character from expression, translating characters in from-string to the corresponding character in to-string. For each character in expression, the same character is searched for in from-string. If the character is found to be the nth character in from-string, the resulting string will contain the nth character from to-string. If to-string is less than n characters long, the resulting string will contain the pad character. If the character is not found in from-string, it is moved to the result string untranslated.
Translation is done on a byte basis and, if used improperly, may result in an invalid mixed string.
If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
TRANSLATE('abcdef')
Returns the value 'ABCDEF'.
TRANSLATE(
)
Returns the value
TRANSLATE(:SITE, '$', 'L')
Returns the value 'Pivabiska $ake Place'.
TRANSLATE(:SITE, '$$', 'Ll')
Returns the value 'Pivabiska $ake P$ace'.
TRANSLATE(:SITE, 'pLA', 'Place', '.')
Returns the value 'pivAbiskA LAk. pLA..'.
>>-TRIM--(--+-------------------------------------------+---expression--)-->
| .-BOTH-----. |
'-+----------+---+-----------------+--FROM--'
+-B--------+ '-strip-character-'
+-LEADING--+
+-L--------+
+-TRAILING-+
'-T--------'
>--------------------------------------------------------------><
|
The TRIM function removes blanks or another specified character from the end or beginning of a string expression.
The expression must be a string expression.
The first argument, if specified, indicates whether characters are removed from the end or beginning of the string. If the first argument is not specified, then the characters are removed from both the end and the beginning of the string.
The second argument, if specified, is a single-character constant that indicates the binary, SBCS, or DBCS character that is to be removed. If expression is a binary string, the second argument must be a binary string constant. If expression is a DBCS graphic or DBCS-only string, the second argument must be a graphic constant consisting of a single DBCS character. If the second argument is not specified then:
The data type of the result depends on the data type of
expression:
| Data type of expression | Data type of the Result |
|---|---|
| CHAR or VARCHAR | VARCHAR |
| GRAPHIC or VARGRAPHIC | VARGRAPHIC |
| BLOB | BLOB |
| CLOB | CLOB |
| DBCLOB | DBCLOB |
The length attribute of the result is the same as the length attribute of expression. The actual length of the result is the length of the expression minus the number of bytes removed. If all characters are removed, the result is an empty string.
The CCSID of the result is the same as that of the string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
TRIM(:HELLO) Results in: 'Hello'. TRIM( TRAILING FROM :HELLO) Results in: ' Hello'.
TRIM( L '0' FROM :BALANCE ) Results in: '345.50'
TRIM( BOTH
FROM
)
Results in:
>>--+-TRUNCATE-+---(--expression1--,--expression2----)---------><
'-TRUNC----'
|
The TRUNCATE function returns expression1 truncated to expression2 places to the right of the decimal point if expression2 is positive or to the left of the decimal point if expression2 is zero or negative.
If expression2 is negative, expression1 is truncated to the absolute value of expression2+1 number of places to the left of the decimal point.
If the absolute value of expression2 is larger than the number of digits to the left of the decimal point, the result is 0. For example, TRUNCATE(748.58,-4) = 0.
The data type and length attribute of the result are the same as the data type and length attribute of the first argument.
If either argument can be null, the result can be null. If either argument is null, the result is the null value.
SELECT TRUNCATE(MAX(SALARY/12, 2) FROM EMP
Because the highest paid employee in the sample employee table earns $52750.00 per year, the example returns the value 4395.83.
SELECT TRUNCATE(873.726, 2),
TRUNCATE(873.726, 1),
TRUNCATE(873.726, 0),
TRUNCATE(873.726, -1),
TRUNCATE(873.726, -2),
TRUNCATE(873.726, -3)
FROM TABLEX
This example returns:
0873.720 0873.700 0873.000 0870.000 0800.000 0000.000
respectively.
SELECT TRUNCATE( 3.5, 0),
TRUNCATE( 3.1, 0),
TRUNCATE(-3.1, 0),
TRUNCATE(-3.5, 0)
FROM TABLEX
This example returns:
3.0 3.0 -3.0 -3.0
respectively.
>>-+-UCASE-+--(--expression--)--------------------------------->< '-UPPER-' |
The UCASE or UPPER function returns a string in which all the characters have been converted to uppercase characters, based on the CCSID of the argument. Only SBCS and UCS-2 graphic characters are converted. Refer to the National Language Support Reference Volume 2, SE09-8002, for a description of the monocasing tables that are used for this translation.
The argument must be an expression whose value is a character string or a graphic string.
The result of the function has the same data type, length attribute, actual length, and CCSID as the argument. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
UCASE('abcdef')
Returns the value 'ABCDEF'.
UPPER(
)
Returns the value:
.------------------.
V |
>>-VALUE--(--expression-----,--expression---+---)--------------><
|
The VALUE function is identical to the COALESCE scalar function. For more information, see "COALESCE".
Character to Varchar
>>-VARCHAR (--character-expression------------------------------>
>-----+-----------------------------------+--)-----------------><
'-,--+-length--+---+-------------+--'
'-DEFAULT-' '-,--integer--'
Graphic to Varchar
>>-VARCHAR (--graphic-expression-------------------------------->
>-----+-----------------------------------+--)-----------------><
'-,--+-length--+---+-------------+--'
'-DEFAULT-' '-,--integer--'
Integer to Varchar >>-VARCHAR---(--integer-expression--)-------------------------->< Decimal to Varchar
>>-VARCHAR------------------------------------------------------>
>----(--decimal-expression--+-----------------------+---)------><
'-,--decimal-character--'
Floating-point to Varchar
>>-VARCHAR------------------------------------------------------>
>----(--floating-point-expression--+-----------------------+---)--><
'-,--decimal-character--'
|
The VARCHAR function returns a character-string representation of:
The result of the function is a varying-length string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Character to Varchar
If the second argument is not specified or DEFAULT is specified:
The actual length of the result is the minimum of the length attribute of the result and the actual length of character-expression. If the length of the character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the truncated characters were all blanks.
If the third argument is not specified then:
Graphic to Varchar
If the second argument is not specified or DEFAULT is specified, the length attribute of the result is determined as follows (where n is the length attribute of the first argument):
The actual length of the result is the minimum of the length attribute of the result and the actual length of graphic-expression. If the length of the character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the truncated characters were all blanks.
If the third argument is not specified, the CCSID of the result is the default CCSID at the current server. If the default CCSID is mixed data, then the result is mixed data. If the default CCSID is SBCS data, then the result is SBCS data.
Integer to Varchar
The result is a varying-length character string of the argument in the form of an SQL integer constant. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. It is left justified.
The actual length of the result is the smallest number of characters that can be used to represent the value of the argument. Leading zeroes are not included. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit.
The CCSID of the result is the default SBCS CCSID at the current server.
Decimal to Varchar
The result is a varying-length character string representation of the argument. The result includes a decimal character and up to p digits, where p is the precision of the decimal-expression with a preceding minus sign if the argument is negative. Leading zeros are not returned. Trailing zeros are returned.
The length attribute of the result is 2+p where p is the precision of the decimal-expression. The actual length of the result is the smallest number of characters that can be used to represent the result, except that trailing characters are included. Leading zeros are not included. If the argument is negative, the result begins with a minus sign. Otherwise, the result begins with a digit.
The CCSID of the result is the default SBCS CCSID at the current server.
Floating-point to Varchar
The result is a varying-length character string representation of the argument in the form of a floating-point constant.
The length attribute of the result is 24. The actual length of the result is the smallest number of characters that can represent the value of the argument such that the mantissa consists of a single digit other than zero followed by the decimal-character and a sequence of digits. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If the argument is zero, the result is 0E0.
The CCSID of the result is the default SBCS CCSID at the current server.
Make EMPNO varying-length with a length of 10.
SELECT VARCHAR(EMPNO,10) INTO :VARHV FROM EMPLOYEE
>>-VARGRAPHIC--(--expression------------------------------------>
>-----+-----------------------------------+--)-----------------><
'-,--+-length--+---+-------------+--'
'-DEFAULT-' '-,--integer--'
|
The VARGRAPHIC function returns a graphic string representation of a string expression.
The first argument must be a string expression. It cannot be a BLOB. Additionally it cannot be CHAR or VARCHAR bit data.
The second argument, if specified as length, is the length attribute of the result and must be an integer constant between 1 and 16370 if the first argument is not nullable or between 1 and 16369 if the first argument is nullable.
If the second argument is not specified, or if DEFAULT is specified, the length attribute of the result is the same as the length attribute of the first argument.
In the following rules, S denotes one of the following:
If the third argument is specified, the CCSID of the result is the third argument. It must be a DBCS or UCS-2 CCSID. The CCSID cannot be 65535.
If the third argument is not specified, and the first argument is character, then the CCSID of the result is determined by a mixed CCSID. Let M denote that mixed CCSID. M is determined as follows:
The following table summarizes the result CCSID based on M.
| M | Result CCSID | Description | DBCS Substitution Character |
|---|---|---|---|
| 930 | 300 | Japanese EBCDIC | X'FEFE' |
| 933 | 834 | Korean EBCDIC | X'FEFE' |
| 935 | 837 | S-Chinese EBCDIC | X'FEFE' |
| 937 | 835 | T-Chinese EBCDIC | X'FEFE' |
| 939 | 300 | Japanese EBCDIC | X'FEFE' |
| 5026 | 4396 | Japanese EBCDIC | X'FEFE' |
| 5035 | 4396 | Japanese EBCDIC | X'FEFE' |
If the third argument is not specified, and the first argument is not a character string, then the CCSID of the result is the same as the CCSID of the first argument.
The result of the function is a varying-length graphic string. If the expression can be null, the result can be null. If the expression is null, the result is the null value. If the expression is an empty string or the EBCDIC string X'0E0F', the result is an empty string.
The actual length of the result depends on the number of characters in the argument. Each character of the argument determines a character of the result. If the length attribute of the resulting varying-length string is less than the actual length of the first argument, truncation is performed and no warning is returned.
If the result is DBCS-graphic data, the equivalence of SBCS and DBCS characters depends on M. Regardless of the CCSID, every double-byte code point in the argument is considered a DBCS character, and every single-byte code point in the argument is considered an SBCS character with the exception of the EBCDIC mixed data shift codes X'0E' and X'0F'.
If the result is UCS-2 graphic data, each character of the argument determines a character of the result. The nth character of the result is the UCS-2 equivalent of the nth character of the argument.
Using the EMPLOYEE table, set the host variable VAR_DESC (vargraphic(24)) to the VARGRAPHIC equivalent of the first name (FIRSTNME) for employee number (EMPNO) '000050'.
SELECT VARGRAPHIC(FIRSTNME) INTO :VAR_DESC FROM EMPLOYEE WHERE EMPNO = '000050'
>>-WEEK--(--expression--)-------------------------------------->< |
The WEEK function returns an integer between 1 and 54 which represents the week of the year. The basic accounting calendar is used. The week starts with Sunday, and January 1 is always in the first week.
The argument must be a date or a timestamp.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
Using the PROJECT table, set the host variable WEEK (int) to the week that project ('PL2100') ended.
SELECT WEEK(PRENDATE) INTO :WEEK FROM PROJECT WHERE PROJNO = 'PL2100'
Results in WEEK being set to 38 when using the sample table.
.------------------.
V |
>>-XOR---(--expression-----,--expression---+---)---------------><
|
The XOR function returns a string that is the logical XOR of the argument strings. This function takes the first argument string, does an XOR comparison with the next string, and then continues to do XOR comparisons for each successive argument using the previous result. If an argument is encountered that is shorter than the previous result, it is padded with blanks.
The arguments must be character strings but cannot be LOBs. The arguments cannot be mixed data character strings or graphic strings. There must be two or more arguments. Arguments other than the first may be parameter markers.
The arguments are converted, if necessary, to the attributes of the result. The attributes of the result are determined as follows:
If an argument can be null, the result can be null; if an argument is null, the result is the null value.
Assume the host variable L1 is a character(2) host variable with a value of X'E1E1', host variable L2 is a character(3) host variable with a value of X'F0F000', and host variable L3 is a character(4) host variable with a value of X'0000000F'.
XOR(:L1,:L2,:L3)
Returns the value X'1111404F'. In this case, the shorter results are padded with blanks (X'40'), so the logical XOR result differs from the result in the following example.
XOR(:L3,:L2,:L1)
Returns the value X'1111400F'.
>>-YEAR--(--expression--)-------------------------------------->< |
The YEAR function returns the year part of a value.
The argument must be a date, timestamp, date duration, or timestamp duration.
The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The other rules depend on the data type of the argument:
The result is the year part of the value, which is an integer between 1 and 9999.
The result is the year part of the value, which is an integer between -9999 and 9999. A nonzero result has the same sign as the argument.
SELECT * FROM PROJECT WHERE YEAR(PRSTDATE) = YEAR(PRENDATE)
SELECT * FROM PROJECT WHERE YEAR(PRENDATE - PRSTDATE) < 1
Numeric to Zoned Decimal
>>-ZONED-------------------------------------------------------->
>----(--numeric-expression----+----------------------------------------------+---)->
'-,--precision-integer--+-------------------+--'
'-,--scale-integer--'
>--------------------------------------------------------------><
Character to Zoned Decimal
>>-ZONED-------------------------------------------------------->
>----(--character-expression----+-------------------------------------------------------------------------+---)->
'-,--precision-integer--+----------------------------------------------+--'
'-,--scale-integer--+-----------------------+--'
'-,--decimal-character--'
>--------------------------------------------------------------><
|
The ZONED function returns a zoned decimal representation of:
The result of the function is a zoned decimal number with precision of p and scale of s, where p and s are the second and third arguments. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
Numeric to Zoned Decimal
The default for precision-integer depends on the data type of the numeric-expression:
The result is the same number that would occur if the first argument were assigned to a NUMERIC column or variable with a precision of p and a scale of s. An error occurs if the number of significant decimal digits required to represent the whole part of the number is greater than p-s.
Character to Zoned Decimal
The result is the same number that would result from CAST(character-expression AS NUMERIC(p,s)). Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming a floating-point, integer, or decimal constant. Digits are truncated from the end if the number of digits right of the decimal character is greater than the scale s. An error occurs if the number of significant digits to the left of the decimal character (the whole part of the number) in character-expressionis greater than p-s. The default decimal character is not valid in the substring if the decimal-character argument is specified.
ZONED(:Z1,15,14)
Returns the value 1.12300000000000.
ZONED(:Z1,11,2)
Returns the value 1123.00.
ZONED(:Z1,4)
Returns the value 1123.