db2函数祥解


1 第一章 聚集函数 1.1 AVG…………………………………………………………………….平均数 1.2 CORRELATION……………………………………………………….返回系数 1.3 COUNT…………………………………………………………………统计函数 1.4 COVARIANCE…………………………………………………………协方差函数 1.5 GROUPING…………………………………………………………….分组函数 1.6 MAX…………………………………………………………………….最大值 1.7 MIN……………………………………………………………………..最小值 1.8 Regression……………………………………………………………….回归函数 1.9 STDDEV…………………………………………………………………偏差函数 1.10 SUM……………………………………………………………………..求和函数 1.11 VARIANCE……………………………………………………………..方差函数 第二章 标量函数 2.1 ABS……………………………………………………………………….绝对值 2.2 ASCII……………………………………………………………………..ASCII 值 2.3 BLOB……………………………………………………………………..返回 BLOB 值 2.4 CEIL………………………………………………………………………最小整数值 2.5 CHAR……………………………………………………………………..转换字符串 2.6 CHR……………………………………………………………………….与 ASCII 相反 2.7 CLOB……………………………………………………………………. 返回 CLOB 值 2.8 COALESCE………………………………………………………………判断是否为空 2.9 CONCAT………………………………………………………………….字符串拼接 2.10 COS……………………………………………………………………….余弦函数 2.11 COSH……………………………………………………………………...弧度函数 2.12 COT………………………………………………………………………..余切函数 2.13 DATE……………………………………………………………………….日期函数 2.14 DAY…………………………………………………………………………返回天数 2.15 DAYNAME………………………………………………………………….返回星期 2.16 DAYOFWEEK……………………………………………………………一周内第 N 天 2.17 DAYOFWEEK_ISO………………………………………………………一周内第 N 天 2.18 DAYOFYEAR…………………………………………………………….一年内第 N 天 2.19 DAYS………………………………………………………………………返回累计天数 2.20 DBCLOB………………………………………………………………返回 DBCLOB 值 2.21 DECIMAL…………………………………………………………返回十进制表示的值 2.22 DECRYPT_BIN……………………………………………………………数据加密函数 2.23 DECRYPT_CHAR…………………………………………………………数据加密函数 2.24 DEGREES……………………………………………………………….. ….返回弧度值 2.25 DIGITS……………………………………………………………….用字符串表示数值 2.26 DOUBLE……………………………………………………………..返回双精度浮点型 2.27 ENCRYPT…………………………………………………………………数据加密函数 2.28 EVENT_MON_STATE…………………………………………….返回事件监视器状态 2 2.29 EXP……………………………………………………………………………..指数函数 2.30 FLOAT……………………………………………………………. …返回单精度浮点型 2.31 FLOOR……………………………………………………………………….最大整数值 2.32 GETHINT………………………………………………………………..取加密后的数据 2.33 GENERATE_UNIQUE………………………………………………….生成唯一值函数 2.34 GRAPHIC………………………………………………………………….返回媒体类型 2.35 HEX………………………………………………………………返回 16 进制表示的值 2.36 HOUR………………………………………………………………..返回时间中的小时 2.37 INSERT……………………………………………………………………查找替换函数 2.38 LOCATE………………………………………………………………………..查找函数 2.39 INTEGER………………………………………………………………………取整函数 2.40 LENGTH…………………………………………………………………….取长度函数 2.41 LONG_VARCHAR……………………………………………………….返回长字符型 2.42 LONG_VARGRAPHIC……………………………………………………返回长媒体型 2.43 LTRIM………………………………………………………………………..去左边空格 2.44 RTRIM………………………………………………………………………..去右边空格 2.45 3 1.1 AVG aggregate function >>-AVG--(--+----------+--expression--)------------------------->< '-DISTINCT-' The AVG function returns the average of a set of numbers. Examples: Using the PROJECT table, set the host variable AVERAGE (decimal(5,2)) to the average staffing level (PRSTAFF) of projects in department (DEPTNO) 'D11'. SELECT AVG(PRSTAFF) INTO :AVERAGE FROM PROJECT WHERE DEPTNO = 'D11' Results in AVERAGE being set to 4.25 (that is 17/4) when using the sample table. Using the PROJECT table, set the host variable ANY_CALC (decimal(5,2)) to the average of each unique staffing level value (PRSTAFF) of projects in department (DEPTNO) 'D11'. SELECT AVG(DISTINCT PRSTAFF) INTO :ANY_CALC FROM PROJECT WHERE DEPTNO = 'D11' Results in ANY_CALC being set to 4.66 (that is 14/3) when using the sample table. 1.2 CORRELATION aggregate function >>-+-CORRELATION-+--(--expression1--,--expression2--)---------->< '-CORR--------' The CORRELATION function returns the coefficient of correlation of a set of number pairs. The argument values must be numbers. The data type of the result is double-precision floating point. The result can be null. When not null, the result is between -1 and 1. The function is applied to the set of (expression1, expression2) pairs derived from the argument values by the elimination of all pairs for which either expression1 or expression2 is null. If the function is applied to an empty set, or if either STDDEV(expression1) or STDDEV(expression2) is equal to zero, the result is a null value. Otherwise, the result is the correlation coefficient for the value pairs in the set. The result is equivalent to the following expression: COVARIANCE(expression1,expression2)/ 4 (STDDEV(expression1)* STDDEV(expression2)) The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type. Example: Using the EMPLOYEE table, set the host variable CORRLN (double-precision floating point) to the correlation between salary and bonus for those employees in department (WORKDEPT) 'A00'. SELECT CORRELATION(SALARY, BONUS) INTO :CORRLN FROM EMPLOYEE WHERE WORKDEPT = 'A00' CORRLN is set to approximately 9.99853953399538E-001 when using the sample table. 1.3 COUNT aggregate function >>-COUNT--(--+-+----------+--expression-+--)------------------->< | '-DISTINCT-' | '-*------------------------' The COUNT function returns the number of rows or values in a set of rows or values. Examples: Using the EMPLOYEE table, set the host variable FEMALE (int) to the number of rows where the value of the SEX column is 'F'. SELECT COUNT(*) FROM EMPLOYEE 1.4 COVARIANCE aggregate function >>-+-COVARIANCE-+--(--expression1--,--expression2--)----------->< '-COVAR------' The COVARIANCE function returns the (population) covariance of a set of number pairs. The argument values must be numbers. The data type of the result is double-precision floating point. The result can be null. The function is applied to the set of (expression1,expression2) pairs derived from the argument values by the elimination of all pairs for which either expression1 or expression2 is null. If the function is applied to an empty set, the result is a null value. Otherwise, the result is the covariance of the value pairs in the set. The result is equivalent to the following: 5 Let avgexp1 be the result of AVG(expression1) and let avgexp2 be the result of AVG(expression2). The result of COVARIANCE(expression1, expression2) is AVG( (expression1 - avgexp1) * (expression2 - avgexp2 ) The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type. Example: Using the EMPLOYEE table, set the host variable COVARNCE (double-precision floating point) to the covariance between salary and bonus for those employees in department (WORKDEPT) 'A00'. SELECT COVARIANCE(SALARY, BONUS) INTO :COVARNCE FROM EMPLOYEE WHERE WORKDEPT = 'A00' COVARNCE is set to approximately 1.68888888888889E+006 when using the sample table. 1.5 GROUPING aggregate function 分组函数 >>-GROUPING--(--expression--)---------------------------------->< ID NAME BZ RQ SJ ddd austin good 2006-10-1 13855134447 yyy austin good 2006-10-1 13855134447 y aust good 2006-10-1 13956967079 eew jack very 2006-10-2 13003000739 001 刘飞 2006-10-10 135567894332 009 刘 HAO 135567894332 111 屹通 XX (样表一) 按照 SJ 分组 SELECT grouping(sj) FROM test group by sj 则把手机号码相同的分为一组,执行结果为五组 1.6 MAX aggregate function 取最大值函数 >>-MAX--(--+----------+--expression--)------------------------->< 6 The MAX function returns the maximum value in a set of values. Examples: ID NAME RQ CJ XM yyy austin 2006-10-1 97.37 4768 y aust 2006-10-1 87.37 4986 eew jack 2006-10-2 8654 ddd austin 2006-10-1 123543 111 屹通 77777 009 刘 22345 001 刘飞 2006-10-10 45.74 2sw3 (样表二) 执行语句: select max(cj) from test 结果为:97.37 1.7 MIN aggregate function 取最小值函数 >>-MIN--(--+----------+--expression--)------------------------->< Examples: select min(cj) from test 结果为:45.74 1.8 Regression functions 回归函数 >>-+-REGR_AVGX----------+--(--expression1--,--expression2--)--->< +-REGR_AVGY----------+ +-REGR_COUNT---------+ +-+-REGR_INTERCEPT-+-+ | '-REGR_ICPT------' | +-REGR_R2------------+ +-REGR_SLOPE---------+ +-REGR_SXX-----------+ +-REGR_SXY-----------+ '-REGR_SYY-----------' The regression functions support the fitting of an ordinary-least-squares regression line of the form y = a * x + b to a set of number pairs. The first element of each pair (expression1) is interpreted as a value of the dependent variable (that is, a "y value"). The second element of each pair (expression2 ) is interpreted as a value of the independent variable (that is, an "x value"). 7 The REGR_COUNT function returns the number of non-null number pairs used to fit the regression line (see below). The REGR_INTERCEPT (or REGR_ICPT) function returns the y-intercept of the regression line ("b" in the above equation). The REGR_R2 function returns the coefficient of determination ("R-squared" or "goodness-of-fit") for the regression. The REGR_SLOPE function returns the slope of the line ("a" in the above equation). The REGR_AVGX, REGR_AVGY, REGR_SXX, REGR_SXY, and REGR_SYY functions return quantities that can be used to compute various diagnostic statistics needed for the evaluation of the quality and statistical validity of the regression model (see below). The argument values must be numbers. The data type of the result of REGR_COUNT is integer. For the remaining functions, the data type of the result is double precision floating point. The result can be null. When not null, the result of REGR_R2 is between 0 and 1, and the result of both REGR_SXX and REGR_SYY is non-negative. Each function is applied to the set of (expression1, expression2) pairs derived from the argument values by the elimination of all pairs for which either expression1 or expression2 is null. If the set is not empty and VARIANCE(expression2) is positive, REGR_COUNT returns the number of non-null pairs in the set, and the remaining functions return results that are defined as follows: REGR_SLOPE(expression1,expression2) = COVARIANCE(expression1,expression2)/VARIANCE(expression2) REGR_INTERCEPT(expression1, expression2) = AVG(expression1) - REGR_SLOPE(expression1, expression2) * AVG(expression2) REGR_R2(expression1, expression2) = POWER(CORRELATION(expression1, expression2), 2) if VARIANCE(expression1)>0 REGR_R2(expression1, expression2) = 1 if VARIANCE(expression1)=0 REGR_AVGX(expression1, expression2) = AVG(expression2) REGR_AVGY(expression1, expression2) = AVG(expression1) REGR_SXX(expression1, expression2) = REGR_COUNT(expression1, expression2) * VARIANCE(expression2) REGR_SYY(expression1, expression2) = REGR_COUNT(expression1, expression2) * VARIANCE(expression1) REGR_SXY(expression1, expression2) = 8 REGR_COUNT(expression1, expression2) * COVARIANCE(expression1, expression2) If the set is not empty and VARIANCE(expression2) is equal to zero, then the regression line either has infinite slope or is undefined. In this case, the functions REGR_SLOPE, REGR_INTERCEPT, and REGR_R2 each return a null value, and the remaining functions return values as defined above. If the set is empty, REGR_COUNT returns zero and the remaining functions return a null value. The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type. The regression functions are all computed simultaneously during a single pass through the data. In general, it is more efficient to use the regression functions to compute the statistics needed for a regression analysis than to perform the equivalent computations using ordinary column functions such as AVERAGE, VARIANCE, COVARIANCE, and so forth. The usual diagnostic statistics that accompany a linear-regression analysis can be computed in terms of the above functions. For example: Adjusted R2 1 - ( (1 - REGR_R2) * ((REGR_COUNT - 1) / (REGR_COUNT - 2)) ) Standard error SQRT( (REGR_SYY-(POWER(REGR_SXY,2)/REGR_SXX))/(REGR_COUNT-2) ) Total sum of squares REGR_SYY Regression sum of squares POWER(REGR_SXY,2) / REGR_SXX Residual sum of squares (Total sum of squares)-(Regression sum of squares) t statistic for slope REGR_SLOPE * SQRT(REGR_SXX) / (Standard error) t statistic for y-intercept REGR_INTERCEPT/((Standard error) * SQRT((1/REGR_COUNT)+(POWER(REGR_AVGX,2)/REGR_SXX)) Example: Using the EMPLOYEE table, compute an ordinary-least-squares regression line that expresses the bonus of an employee in department (WORKDEPT) 'A00' as a linear function of the employee's salary. Set the host variables SLOPE, ICPT, RSQR (double-precision floating point) to the slope, intercept, and coefficient of determination of the regression line, respectively. Also set the host variables AVGSAL and AVGBONUS to the average salary and average bonus, respectively, of the employees in department 'A00', and set the host variable CNT (integer) to the number of employees in department 'A00' for whom both salary and bonus data are available. Store the remaining regression statistics in host variables SXX, SYY, and SXY. SELECT REGR_SLOPE(BONUS,SALARY), REGR_INTERCEPT(BONUS,SALARY), 9 REGR_R2(BONUS,SALARY), REGR_COUNT(BONUS,SALARY), REGR_AVGX(BONUS,SALARY), REGR_AVGY(BONUS,SALARY), REGR_SXX(BONUS,SALARY), REGR_SYY(BONUS,SALARY), REGR_SXY(BONUS,SALARY) INTO :SLOPE, :ICPT, :RSQR, :CNT, :AVGSAL, :AVGBONUS, :SXX, :SYY, :SXY FROM EMPLOYEE WHERE WORKDEPT = 'A00' When using the sample table, the host variables are set to the following approximate values: SLOPE: +1.71002671916749E-002 ICPT: +1.00871888623260E+002 RSQR: +9.99707928128685E-001 CNT: 3 AVGSAL: +4.28333333333333E+004 AVGBONUS: +8.33333333333333E+002 SXX: +2.96291666666667E+008 SYY: +8.66666666666667E+004 SXY: +5.06666666666667E+006 1.9 STDDEV aggregate function 求平均差值函数 >>-STDDEV--(--+----------+--expression--)---------------------->< The STDDEV function returns the standard deviation of a set of numbers. The argument values must be numbers. The data type of the result is double-precision floating point. The result can be null. Example: 使用 (样表二) SELECT STDDEV(cj) FROM TEST 结果为:22.3574839570308 ====================================================================== =========================================== 1.10 SUM aggregate function 求和函数 >>-SUM--(--+----------+--expression--)------------------------->< The SUM function returns the sum of a set of numbers. Example: 使用 (样表二) SELECT SUM(cj) FROM TEST 结果为:230.48 ============================================ 10 1.11 VARIANCE aggregate function 方差函数 >>-+-VARIANCE-+--(--+----------+--expression--)---------------->< The VARIANCE function returns the variance of a set of numbers. The argument values must be numbers. Example: Using the EMPLOYEE table, set the host variable VARNCE (double-precision floating point) to the variance of the salaries for those employees in department (WORKDEPT) 'A00'. SELECT VARIANCE(SALARY) INTO :VARNCE FROM EMPLOYEE WHERE WORKDEPT = 'A00' 结果为: 98763888.88. 第二章 标量函数 2.1 ABS or ABSVAL scalar function 求绝对值 >>-+-ABS----+--(--expression--)-------------------------------->< '-ABSVAL-' Example: ABS(-51234) 结果为:51234. ====================================================================== =============================== 2.2 ASCII scalar function 求 ASCII >>-ASCII--(--expression--)------------------------------------->< 返回整数参数最左边的字符的 ASCII 码 select ascii(name) from test where id='ddd' 结果为:97 2.3 BLOB scalar function >>-BLOB--(--string-expression--+------------+--)--------------->< The BLOB function returns a BLOB representation of a string of any type. string-expression 11 A string-expression whose value can be a character string, graphic string, or a binary string. integer An integer value specifying the length attribute of the resulting BLOB data type. If integer is not specified, the length attribute of the result is the same as the length of the input, except where the input is graphic. In this case, the length attribute of the result is twice the length of the input. The result of the function is a BLOB. If the argument can be null, the result can be null; if the argument is null, the result is the null value. Examples Given a table with 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. SELECT BLOB(MAP_NAME || ': ') || TOPOGRAPHIC_MAP FROM ONTARIO_SERIES_4 WHERE TOPOGRAPHIC_MAP LIKE BLOB('%Pellow Island%') 2.4 CEILING or CEIL scalar function >>-+-CEILING-+--(--expression--)------------------------------->< '-CEIL----' 返回比参数大或等于参数的最小的整数值 Examples:使用(样表二) select name,cj,ceil(cj)from test 结果为: NAME CJ 3 屹通 刘飞 45.74 46 刘 jack austin 97.37 98 austin aust 87.37 88 2.5 CHAR scalar function Character to Character: >>-CHAR--(--character-expression--+------------+--)------------>< '-,--integer-' Datetime to Character: >>-CHAR--(--datetime-expression--+--------------+--)----------->< 12 '-,--+-ISO---+-' +-USA---+ +-EUR---+ +-JIS---+ '-LOCAL-' 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 fixed-length character string representation of: 注意: The CAST expression can also be used to return a string expression. 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. Examples: 使用(样表一) select char(rq,usa) from test where id='ddd' 结果为:10/01/2006 select char(rq,iso) from test where id='ddd' 结果为:2006-10-01 例子 2: Assume that the PRSTDATE column has an internal value equivalent to 1988-12-25. The following function returns the value '12/25/1988'. CHAR(PRSTDATE, USA) Assume that the STARTING column has an internal value equivalent to 17:12:30, and that the host variable HOUR_DUR (decimal(6,0)) is a time duration with a value of 050000 (that is, 5 hours). The following function returns the value '5:12 PM'. CHAR(STARTING, USA) The following function returns the value '10:12 PM'. 13 CHAR(STARTING + :HOUR_DUR, USA) Assume that the RECEIVED column (TIMESTAMP) has an internal value equivalent to the combination of the PRSTDATE and STARTING columns. The following function returns the value '1988-12-25-17.12.30.000000'. CHAR(RECEIVED) The LASTNAME column is defined as VARCHAR(15). The following function returns the values in this column as fixed-length character strings that are 10 characters long. LASTNAME values that are more than 10 characters long (excluding trailing blanks) are truncated and a warning is returned. SELECT CHAR(LASTNAME,10) FROM EMPLOYEE The EDLEVEL column is defined as SMALLINT. The following function returns the values in this column as fixed-length character strings. An EDLEVEL value of 18 is returned as the CHAR(6) value '18 ' ('18' followed by four blanks). SELECT CHAR(EDLEVEL) FROM EMPLOYEE The SALARY column is defined as DECIMAL with a precision of 9 and a scale of 2. The current value (18357.50) is to be displayed with a comma as the decimal character (18357,50). The following function returns the value '00018357,50'. CHAR(SALARY, ',') Values in the SALARY column are to be subtracted from 20000.25 and displayed with the default decimal character. The following function returns the value '-0001642.75'. CHAR(20000.25 - SALARY) Assume that the host variable SEASONS_TICKETS is defined as INTEGER and has a value of 10000. The following function returns the value '10000.00 '. CHAR(DECIMAL(:SEASONS_TICKETS,7,2)) Assume that the host variable DOUBLE_NUM is defined as DOUBLE and has a value of -987.654321E-35. The following function returns the value '-9.87654321E-33 '. Because the result data type is CHAR(24), there are nine trailing blanks in the result. CHAR(:DOUBLE_NUM) ====================================================================== =================================================================== 2.6 CHR scalar function >>-CHR--(--expression--)--------------------------------------->< 根据 ASCII 值返回字符 例子: select chr(65) from sysibm.sysdummy1 结果为:A 2.7 CLOB scalar function >>-CLOB--(--character-string-expression--+------------+--)----->< '-,--integer-' 14 The CLOB function returns a CLOB representation of a character string type. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. character-string-expression An expression that returns a value that is a character string. integer An integer value specifying the length attribute of the resulting CLOB data type. The value must be between 0 and 2 147 483 647. If integer is not specified, the length of the result is the same as the length of the first argument. The result of the function is a CLOB. If the argument can be null, the result can be null; if the argument is null, the result is the null value. 2.8 COALESCE scalar function >>-COALESCE-------(--expression----,--expression-+--)---------->< 判断字段是否为空,如果为空侧返回一个值 Examples: 使用(样表一)ID=’111’的记录 BZ 字段为空 select COALESCE(bz,'显示的值') from test where id='111' 结果为:显示的值 2.9 CONCAT scalar function >>-CONCAT-------(--expression1--,--expression2--)-------------->< 注意: || may be used as a synonym for CONCAT. The schema is SYSIBM. 返回 2 个值串相连,但是这 2 个值类型必须相同. 例子: select id,concat(name,'测试') from test where id='001' 结果为:刘飞 测试 2.10 COS scalar function >>-COS--(--expression--)--------------------------------------->< The schema is SYSIBM. (The SYSFUN version of the COS function continues to be available.) Returns the cosine of the argument, where the argument is an angle expressed in radians. The argument can be of any built-in numeric type. It is converted to a double-precision 15 floating-point number for processing by the function. The result of the function is a double-precision floating-point number. The result can be null if the argument can be null or the database is configured with DFT_SQLMATHWARN set to YES; the result is the null value if the argument is null. 2.11 COSH scalar function 弧度函数 >>-COSH--(--expression--)-------------------------------------->< The schema is SYSIBM. Returns the hyperbolic cosine of the argument, where the argument is an angle expressed in radians. The argument can be of any built-in numeric data type. It is converted to a double-precision floating-point number for processing by the function. The result of the function is a double-precision floating-point number. The result can be null if the argument can be null or the database is configured with DFT_SQLMATHWARN set to YES; the result is the null value if the argument is null. 2.12 COT scalar function 余切函数 >>-COT--(--expression--)--------------------------------------->< The schema is SYSIBM. (The SYSFUN version of the COT function continues to be available.) Returns the cotangent of the argument, where the argument is an angle expressed in radians. The argument can be of any built-in numeric type. It is converted to a double-precision floating-point number for processing by the function. The result of the function is a double-precision floating-point number. The result can be null if the argument can be null or the database is configured with DFT_SQLMATHWARN set to YES; the result is the null value if the argument is null. 2.13 DATE scalar function 日期函数 >>-DATE--(--expression--)-------------------------------------->< 16 返回一个日期格式的值 The argument must be a date, timestamp, a positive number less than or equal to 3 652 059, a valid string representation of a date or timestamp, or a string of length 7 that is not a CLOB, LONG VARCHAR, DBCLOB, or LONG VARGRAPHIC. 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: If the argument is a date, timestamp, or valid string representation of a date or timestamp: The result is the date part of the value. If the argument is a number: The result is the date that is n-1 days after January 1, 0001, where n is the integral part of the number. If the argument is a string with a length of 7: The result is the date represented by the string. Examples: Assume that the column RECEIVED (timestamp) has an internal value equivalent to '1988-12-25-17.12.30.000000'. This example results in an internal representation of '1988-12-25'. DATE(RECEIVED) This example results in an internal representation of '1988-12-25'. DATE('1988-12-25') This example results in an internal representation of '1988-12-25'. DATE('25.12.1988') This example results in an internal representation of '0001-02-04'. DATE(35) 2.14 DAY scalar function 日期函数 >>-DAY--(--expression--)--------------------------------------->< The DAY function returns the day part of a value. The argument must be a date, timestamp, date duration, timestamp duration, or a valid character string representation of a date or timestamp that is neither a CLOB nor a LONG VARCHAR. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. 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. 17 Examples: 使用表 TEST,日期 2006-10-2 select day(‘2006-10-2’) 结果为:2 2.15 DAYNAME scalar function 返回星期 >>-DAYNAME--(--expression--)----------------------------------->< 返回一个日期中的天数,是一周中的星期几 例子: Select dayname(‘2006-10-10’) from test 结果为:星期二 2.16 DAYOFWEEK scalar function >>-DAYOFWEEK--(--expression--)--------------------------------->< 返回该天是一周中的第几天,星期天作为一周的第一天 ====================================================================== ============================================================= 2.17 DAYOFWEEK_ISO scalar function >>-DAYOFWEEK_ISO--(--expression--)----------------------------->< 返回该天是一周中的第几天,星期天作为一周的第一天 2.18 DAYOFYEAR scalar function >>-DAYOFYEAR--(--expression--)--------------------------------->< 返回该天是一年中的第几天 ====================================================================== =============================== 2.19 DAYS scalar function >>-DAYS--(--expression--)-------------------------------------->< 返回一个天数 18 The DAYS function returns an integer representation of a date. The argument must be a date, timestamp, or a valid character string representation of a date or timestamp that is neither a CLOB nor a LONG VARCHAR. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. 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. ====================================================================== =================================================== 2.20 DBCLOB scalar function >>-DBCLOB--(--graphic-expression--+------------+--)------------>< '-,--integer-' 返回一个 DBCLOB 型的类型值 2.21 DECIMAL() 返回一个十进制数值,输入参数可以是十进制数值,整型,单精度浮点型,日期,时间和时 间戳型 Examples: 假设有个当前日期为 2006-11-28 select decimal(current date,10) from sysibm.sysdummy1 结果为:20061128 The following table shows the decimal result and resulting precision and scale for various datetime input values. DECIMAL(arguments) Precision and Scale Result DECIMAL(2000-03-21) (8,0) 20000321 DECIMAL(2000-03-21, 10) (10,0) 20000321 DECIMAL(2000-03-21, 12, 2) (12,2) 20000321.00 DECIMAL(12:02:21) (6,0) 120221 DECIMAL(12:02:21, 10) (10,0) 120221 DECIMAL(12:02:21, 10, 2) (10,2) 120221.00 DECIMAL(2000-03-21-12.02.21.123456) (20, 6) 20000321120221.123456 DECIMAL(2000-03-21-12.02.21.123456, 23) (23, 6) 20000321120221.123456 19 DECIMAL(2000-03-21-12.02.21.123456, 23, 4) (23, 4) 20000321120221.1234 2.22 DECRYPT_BIN and DECRYPT_CHAR scalar functions >>-+-DECRYPT_BIN--+---------------------------------------------> '-DECRYPT_CHAR-' >--(--encrypted-data--+-------------------------------+--)----->< '-,--password-string-expression-' 数据加密函数 The DECRYPT_BIN and DECRYPT_CHAR functions both return a value that is the result of decrypting encrypted-data. The password used for decryption is either the password-string-expression value or the ENCRYPTION PASSWORD value assigned by the SET ENCRYPTION PASSWORD statement. The DECRYPT_BIN and DECRYPT_CHAR functions can only decrypt values that are encrypted using the ENCRYPT function (SQLSTATE 428FE). encrypted-data An expression that returns a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA value as a complete, encrypted data string. The data string must have been encrypted using the ENCRYPT function. password-string-expression An expression that returns a CHAR or VARCHAR value with at least 6 bytes and no more than 127 bytes (SQLSTATE 428FC). This expression must be the same password used to encrypt the data or decryption will result in an error (SQLSTATE 428FD). If the value of the password argument is null or not provided, the data will be encrypted using the ENCRYPTION PASSWORD value, which must have been set for the session (SQLSTATE 51039). The result of the DECRYPT_BIN function is VARCHAR FOR BIT DATA. The result of the DECRYPT_CHAR function is VARCHAR. If the encrypted-data included a hint, the hint is not returned by the function. The length attribute of the result is the length of the data type of the encrypted-data minus 8 bytes. The actual length of the value returned by the function will match the length of the original string that was encrypted. If the encrypted-data includes bytes beyond the encrypted string, these bytes are not returned by the function. 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 data is decrypted on a different system that uses a code page different from the code page in which the data was encrypted, then expansion may occur when converting the decrypted value to the database code page. In such situations, the encrypted-data value should be cast to a VARCHAR string with a larger number of bytes. 20 Examples: Example 1: This example uses the ENCRYPTION PASSWORD value to hold the encryption password. SET ENCRYPTION PASSWORD = 'Ben123'; INSERT INTO EMP(SSN) VALUES ENCRYPT('289-46-8832'); SELECT DECRYPT_CHAR(SSN) FROM EMP; This returns the value '289-46-8832'. Example 2: This example explicitly passes the encryption password. INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832','Ben123',''); SELECT DECRYPT(SSN,'Ben123') FROM EMP; This example returns the value '289-46-8832'. 2.24 DEGREES scalar function >>-DEGREES--(--expression--)----------------------------------->< 弧度函数 The argument can be of any built-in numeric type. It is converted to a double-precision floating-point number for processing by the function. The result of the function is a double-precision floating-point number. The result can be null; if the argument is null, the result is the null value. 2.25 DIGITS scalar function >>-DIGITS--(--expression--)------------------------------------>< 返回一个字符串表达的数值 The DIGITS function returns a character-string representation of a number. The argument must be an expression that returns a value of type SMALLINT, INTEGER, BIGINT or DECIMAL. If the argument can be null, the result can be null; if the argument is null, the result is the null value. 21 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 character. Instead, it consists exclusively of digits, including, if necessary, leading zeros to fill out the string. The length of the string is: 5 if the argument is a small integer 10 if the argument is a large integer 19 if the argument is a big integer p if the argument is a decimal number with a precision of p. Examples: Assume that a table called TABLEX contains an INTEGER column called INTCOL containing 10-digit numbers. List all distinct four digit combinations of the first four digits contained in column INTCOL. SELECT DISTINCT SUBSTR(DIGITS(INTCOL),1,4) FROM TABLEX Assume that COLUMNX has the DECIMAL(6,2) data type, and that one of its values is -6.28. Then, for this value: 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. 2.26 DOUBLE scalar function Numeric to Double: >>-+-DOUBLE-----------+--(--numeric-expression--)-------------->< +-FLOAT------------+ '-DOUBLE_PRECISION-' Character String to Double: >>-DOUBLE--(--string-expression--)----------------------------->< 返回双精度浮点型 Example: 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 is applied to SALARY so that the division is carried out in floating point: 22 SELECT EMPNO, DOUBLE(SALARY)/COMM FROM EMPLOYEE WHERE COMM > 0 2.27 ENCRYPT scalar function >>-ENCRYPT------------------------------------------------------> >--(--data-string-expression--+--------------------------------------------------------------+--)->< '-,--password-string-expression--+---------------------------+-' '-,--hint-string-expression-' 数据加密函数 The ENCRYPT function returns a value that is the result of encrypting data-string-expression. The password used for encryption is either the password-string-expression value or the ENCRYPTION PASSWORD value (as assigned using the SET ENCRYPTION PASSWORD statement). In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. data-string-expression An expression that returns a CHAR or VARCHAR value to be encrypted. The length attribute for the data type of data-string-expression is limited to 32663 without a hint-string-expression argument and 32631 when the hint-string-expression argument is specified (SQLSTATE 42815). password-string-expression An expression that returns a CHAR or VARCHAR value with at least 6 bytes and no more than 127 bytes (SQLSTATE 428FC). The value represents the password used to encrypt the data-string-expression. If the value of the password argument is null or not provided, the data will be encrypted using the ENCRYPTION PASSWORD value, which must have been set for the session (SQLSTATE 51039). hint-string-expression An expression that returns a CHAR or VARCHAR value up to 32 bytes that will help data owners remember passwords (for example, 'Ocean' as a hint to remember 'Pacific'). If a hint value is given, the hint is embedded into the result and can be retrieved using the GETHINT function. If this argument is null or not provided, no hint will be embedded in the result. The result data type of the function is VARCHAR FOR BIT DATA. The length attribute of the result is: When the optional hint parameter is specified, the length attribute of the non-encrypted data + 8 bytes + the number of bytes to the next 8 byte boundary + 32 bytes for the hint length. With no hint parameter, the length attribute of the non-encrypted data + 8 bytes + the number of 23 bytes to the next 8 byte boundary. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value. Notice that the encrypted result is longer than the data-string-expression value. Therefore, when assigning encrypted values, ensure that the target is declared with sufficient size to contain the entire encrypted value. Notes: Encryption Algorithm: The internal encryption algorithm used is RC2 block cipher with padding, the 128-bit secret key is derived from the password using a MD2 message digest. Encryption Passwords and Data: It is the user's responsibility to perform password management. Once the data is encrypted only the password used to encrypt it can be used to decrypt it (SQLSTATE 428FD). Be careful when using CHAR variables to set password values as they may be padded with blanks. The encrypted result may contain null terminator and other non-printable characters. Table Column Definition: When defining columns and types to contain encrypted data, always calculate the length attribute as follows. For encrypted data with no hint: Maximum length of the non-encrypted data + 8 bytes + the number of bytes to the next 8 byte boundary = encrypted data column length. For encrypted data with an embedded hint: Maximum length of the non-encrypted data + 8 bytes + the number of bytes to the next 8 byte boundary + 32 bytes for the hint length = encrypted data column length. Any assignment or cast to a length shorter than the suggested data length may result in failed decryption in the future and lost data. Blanks are valid encrypted data values that may be truncated when stored in a column that is too short. Some sample column length calculations: Maximum length of non-encrypted data 6 bytes 8 bytes 8 bytes Number of bytes to the next 8 byte boundary 2 bytes --------- Encrypted data column length 16 bytes Maximum length of non-encrypted data 32 bytes 8 bytes 8 bytes Number of bytes to the next 8 byte boundary 8 bytes 24 --------- Encrypted data column length 48 bytes Administration of encrypted data: Encrypted data can only be decrypted on servers that support the decryption functions that correspond to the ENCRYPT function. Hence, replication of columns with encrypted data should only be done to servers that support the DECRYPT_BIN or DECRYPT_CHAR function. Examples: Example 1: This example uses the ENCRYPTION PASSWORD value to hold the encryption password. SET ENCRYPTION PASSWORD = 'Ben123'; INSERT INTO EMP(SSN) VALUES ENCRYPT('289-46-8832'); Example 2: This example explicitly passes the encryption password. INSERT INTO EMP(SSN) VALUES ENCRYPT('289-46-8832','Ben123'); Example 3: The hint 'Ocean' is stored to help the user remember the encryption password of 'Pacific'. INSERT INTO EMP(SSN) VALUES ENCRYPT('289-46-8832','Pacific','Ocean'); 2.28 EVENT_MON_STATE scalar function >>-EVENT_MON_STATE--(--string-expression--)-------------------->< 返回事件监视器的状态,0 表示不活动,1 表示活动 The EVENT_MON_STATE function returns the current state of an event monitor. The argument is a string expression with a resulting type of CHAR or VARCHAR and a value that is the name of an event monitor. If the named event monitor does not exist in the SYSCAT.EVENTMONITORS catalog table, SQLSTATE 42704 will be returned. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. The result is an integer with one of the following values: 0 The event monitor is inactive. 1 The event monitor is active. If the argument can be null, the result can be null; if the argument is null, the result is the null value. 25 Example: The following example selects all of the defined event monitors, and indicates whether each is active or inactive: SELECT EVMONNAME, CASE WHEN EVENT_MON_STATE(EVMONNAME) = 0 THEN 'Inactive' WHEN EVENT_MON_STATE(EVMONNAME) = 1 THEN 'Active' END FROM SYSCAT.EVENTMONITORS 2.29 EXP scalar function 指数函数 >>-EXP--(--expression--)--------------------------------------->< Returns the exponential function of the argument. The argument can be of any built-in numeric data type. It is converted to a double-precision floating-point number for processing by the function. The result of the function is a double-precision floating-point number. The result can be null; if the argument is null, the result is the null value. 2.30 FLOAT scalar function >>-FLOAT--(--numeric-expression--)----------------------------->< 返回双精度浮点型 The FLOAT function returns a floating-point representation of a number. FLOAT is a synonym for DOUBLE. 2.32 FLOOR scalar function >>-FLOOR--(--expression--)------------------------------------->< 返回最大整型值,该值大于或者等于该数值 NAME CJ 3 austin 97.37 97 aust 87.37 87 select name,cj,FLOOR(cj) from test 26 2.33 GETHINT scalar function >>-GETHINT--(--encrypted-data--)------------------------------->< 返回密码的提示值 The GETHINT function will return the password hint if one is found in the encrypted-data. A password hint is a phrase that will help data owners remember passwords; for example, 'Ocean' as a hint to remember 'Pacific'. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. encrypted-data An expression that returns a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA value that is a complete, encrypted data string. The data string must have been encrypted using the ENCRYPT function (SQLSTATE 428FE). The result of the function is VARCHAR(32). The result can be null; if the hint parameter was not added to the encrypted-data by the ENCRYPT function or the first argument is null, the result is the null value. Example: In this example the hint 'Ocean' is stored to help the user remember the encryption password 'Pacific'. INSERT INTO EMP (SSN) VALUES ENCRYPT('289-46-8832', 'Pacific','Ocean'); SELECT GETHINT(SSN) FROM EMP; The value returned is 'Ocean'. 2.34 GENERATE_UNIQUE scalar function >>-GENERATE_UNIQUE--(--)--------------------------------------->< The GENERATE_UNIQUE function returns a bit data character string 13 bytes long (CHAR(13) FOR BIT DATA) that is unique compared to any other execution of the same function. (The system clock is used to generate the internal Universal Time, Coordinated (UTC) timestamp along with the partition number on which the function executes. Adjustments that move the actual system clock backward could result in duplicate values.) The function is defined as not-deterministic. There are no arguments to this function (the empty parentheses must be specified). The result of the function is a unique value that includes the internal form of the Universal Time, Coordinated (UTC) and the partition number where the function was processed. The result cannot be null. 27 The result of this function can be used to provide unique values in a table. Each successive value will be greater than the previous value, providing a sequence that can be used within a table. The value includes the partition number where the function executed so that a table partitioned across multiple partitions also has unique values in some sequence. The sequence is based on the time the function was executed. This function differs from using the special register CURRENT TIMESTAMP in that a unique value is generated for each row of a multiple row insert statement or an insert statement with a fullselect. The timestamp value that is part of the result of this function can be determined using the TIMESTAMP scalar function with the result of GENERATE_UNIQUE as an argument. Examples: Create a table that includes a column that is unique for each row. Populate this column using the GENERATE_UNIQUE function. Notice that the UNIQUE_ID column has "FOR BIT DATA" specified to identify the column as a bit data character string. CREATE TABLE EMP_UPDATE (UNIQUE_ID CHAR(13) FOR BIT DATA, EMPNO CHAR(6), TEXT VARCHAR(1000)) INSERT INTO EMP_UPDATE VALUES (GENERATE_UNIQUE(), '000020', 'Update entry...'), (GENERATE_UNIQUE(), '000050', 'Update entry...') This table will have a unique identifier for each row provided that the UNIQUE_ID column is always set using GENERATE_UNIQUE. This can be done by introducing a trigger on the table. CREATE TRIGGER EMP_UPDATE_UNIQUE NO CASCADE BEFORE INSERT ON EMP_UPDATE REFERENCING NEW AS NEW_UPD FOR EACH ROW SNEW_UPD.UNIQUE_ID = GENERATE_UNIQUE() With this trigger defined, the previous INSERT statement could be issued without the first column as follows. INSERT INTO EMP_UPDATE (EMPNO, TEXT) VALUES ('000020', 'Update entry 1...'), ('000050', 'Update entry 2...') The timestamp (in UTC) for when a row was added to EMP_UPDATE can be returned using: SELECT TIMESTAMP (UNIQUE_ID), EMPNO, TEXT FROM EMP_UPDATE Therefore, there is no need to have a timestamp column in the table to record when a row is inserted. 28 2.35 GRAPHIC scalar function Graphic to Graphic: >>-GRAPHIC--(--graphic-expression--+------------+--)----------->< '-,--integer-' Character to Graphic: >>-GRAPHIC--(--character-expression--)------------------------->< Datetime to Graphic: >>-GRAPHIC--(--datetime-expression--+--------------+--)-------->< '-,--+-ISO---+-' +-USA---+ +-EUR---+ +-JIS---+ '-LOCAL-' The GRAPHIC function returns a fixed-length graphic string representation of: A graphic string, if the first argument is any type of graphic string A datetime value (Unicode database only), if the first argument is a date, time, or timestamp In a Unicode database, if a supplied argument is a character string, it is first converted to a graphic string before the function is executed. When the output string is truncated, such that the last character is a high surrogate, that surrogate is converted to the blank character (X'0020'). Do not rely on this behavior, because it might change in a future release. The result of the function is a fixed-length graphic string (GRAPHIC data type). If the first argument can be null, the result can be null; if the first argument is null, the result is the null value. Graphic to Graphic graphic-expression An expression that returns a value that is a graphic string. integer An integer value specifying the length attribute of the resulting GRAPHIC data type. The value must be between 1 and 127. If a value is not specified, the length attribute of the result is the same as the length attribute of the first argument. Character to Graphic character-expression 29 An expression whose value must be of a character string data type other than LONG VARCHAR or CLOB, and whose maximum length is 16 336 bytes. The length attribute of the result is equal to the length attribute of the argument. Datetime to Graphic datetime-expression An expression that is of one of the following three data types: date The result is the graphic string representation of the date in the format specified by the second argument. The length of the result is 10. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703). time The result is the graphic string representation of the time in the format specified by the second argument. The length of the result is 8. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703). timestamp The result is the graphic string representation of the timestamp. The length of the result is 26. The second argument is not applicable and must not be specified (SQLSTATE 42815). The code page of the string is the code page of the database at the application server. Related reference VARGRAPHIC scalar function 2.36 HEX scalar function >>-HEX--(--expression--)--------------------------------------->< 返回一个表示为字符串的值的 16 进制表示. The HEX function returns a hexadecimal representation of a value as a character string. The argument can be an expression that is a value of any built-in data type with a maximum length of 16 336 bytes. 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 code page is the database code page. The result is a string of hexadecimal digits. The first two represent the first byte of the argument, the next two represent the second byte of the argument, and so forth. If the argument is a datetime value or a numeric value the result is the hexadecimal representation of the internal form of the argument. The hexadecimal representation that is returned may be different depending on the application server where the function is executed. Cases where differences would be evident include: 30 Character string arguments when the HEX function is performed on an ASCII client with an EBCDIC server or on an EBCDIC client with an ASCII server. Numeric arguments (in some cases) when the HEX function is performed where client and server systems have different byte orderings for numeric values. The type and length of the result vary based on the type and length of character string arguments. Character string Fixed length not greater than 127 Result is a character string of fixed length twice the defined length of the argument. Fixed length greater than 127 Result is a character string of varying length twice the defined length of the argument. Varying length Result is a character string of varying length with maximum length twice the defined maximum length of the argument. Graphic string Fixed length not greater than 63 Result is a character string of fixed length four times the defined length of the argument. Fixed length greater than 63 Result is a character string of varying length four times the defined length of the argument. Varying length Result is a character string of varying length with maximum length four times the defined maximum length of the argument. Examples: Assume the use of a DB2 for AIX application server for the following examples. Using the DEPARTMENT table set the host variable HEX_MGRNO (char(12)) to the hexadecimal representation of the manager number (MGRNO) for the 'PLANNING' department (DEPTNAME). SELECT HEX(MGRNO) INTO :HEX_MGRNO FROM DEPARTMENT WHERE DEPTNAME = 'PLANNING' HEX_MGRNO will be set to '303030303230' when using the sample table (character value is '000020'). Suppose COL_1 is a column with a data type of char(1) and a value of 'B'. The hexadecimal representation of the letter 'B' is X'42'. HEX(COL_1) returns a two-character string '42'. Suppose COL_3 is a column with a data type of decimal(6,2) and a value of 40.1. An eight-character string '0004010C' is the result of applying the HEX function to the internal representation of the decimal value, 40.1. 2.37 HOUR scalar function >>-HOUR--(--expression--)-------------------------------------->< 31 返回时间值中的小时部分 例子: 有一个字段 TT 为时间戳型,值为 2006-11-9 15:37:11 select hour('2006-11-9 15:37:11') from test 结果为:15 2.38 INSERT scalar function >>-INSERT--(--expression1--,--expression2--,--------------------> >--expression3--,--expression4--)------------------------------>< The schema is SYSFUN. Returns a string where expression3 bytes have been deleted from expression1, beginning at expression2, and where expression4 has been inserted into expression1, beginning at expression2. If the length of the result string exceeds the maximum for the return type, an error is returned (SQLSTATE 38552). The first argument is a character string or a binary string type. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. The second and third arguments must be a numeric value with a data type of SMALLINT or INTEGER. If the first argument is a character string, then the fourth argument must also be a character string. If the first argument is a binary string, then the fourth argument must be a binary string. For a VARCHAR the maximum length is 4 000 bytes and for a CLOB or a binary string the maximum length is 1 048 576 bytes. For the first and fourth arguments, CHAR is converted to VARCHAR and LONG VARCHAR to CLOB(1M), for second and third arguments SMALLINT is converted to INTEGER for processing by the function. The result is based on the argument types as follows: VARCHAR(4000) if both the first and fourth arguments are VARCHAR (not exceeding 4 000 bytes) or CHAR CLOB(1M) if either the first or fourth argument is CLOB or LONG VARCHAR BLOB(1M) if both first and fourth arguments are BLOB. The result can be null; if any argument is null, the result is the null value. Example: Delete one character from the word 'DINING' and insert 'VID', both beginning at the third character. VALUES CHAR(INSERT('DINING', 3, 1, 'VID'), 10) 32 This example returns the following: 1 ---------- DIVIDING As mentioned, the output of the INSERT function is VARCHAR(4000). In this example, the function CHAR has been used to limit the output of INSERT to 10 bytes. The starting location of a particular string can be found using the LOCATE function. ====================================================================== ========================================================== LOCATE scalar function >>-LOCATE--(--search-string--,--source-string--+----------+--)->< '-,--start-' The schema is SYSFUN. Returns the starting position of the first occurrence of search-string within source-string. If the optional start is specified, it indicates the character position in source-string at which the search is to begin. The LOCATE function would then be equivalent to: POSSTR(SUBSTR(source-string, start), search-string) + start - 1 If search-string is not found within source-string, the value 0 is returned. If the first argument is a character string, the second argument must be a character string. For a VARCHAR, the maximum length is 4 000 bytes, and for a CLOB, the maximum length is 1 048 576 bytes. If the first argument is a binary string, the second argument must be a binary string with a maximum length of 1 048 576 bytes. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. The third argument must be INTEGER or SMALLINT. The result of the function is INTEGER. The result can be null; if any argument is null, the result is the null value. Example: Find the location of the letter 'N' (first occurrence) in the word 'DINING'. VALUES LOCATE ('N', 'DINING') This example returns the following: 1 ----------- 3 ====================================================================== ================================================= 33 INTEGER scalar function >>-+-INTEGER-+--(--+-numeric-expression---+--)----------------->< '-INT-----' +-character-expression-+ +-date-expression------+ '-time-expression------' The schema is SYSIBM. The INTEGER function returns an integer representation of a number, character string, date, or time in the form of an integer constant. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. numeric-expression An expression that returns a value of any built-in numeric data type. 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 decimal part of the argument is truncated if present. character-expression An expression that returns a character string value of length not greater than the maximum length of a character constant. Leading and trailing blanks are eliminated and the resulting string must conform to the rules for forming an SQL integer constant (SQLSTATE 22018). The character string cannot be a long string. If the argument is a character-expression, the result is the same number that would occur if the corresponding integer constant were assigned to a large integer column or variable. date-expression An expression that returns a value of the DATE data type. The result is an INTEGER value representing the date as yyyymmdd. time-expression An expression that returns a value of the TIME data type. The result is an INTEGER value representing the time as hhmmss. 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. Examples: 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 employee number (EMPNO). The list should be in descending order of the calculated value. SELECT INTEGER (SALARY / EDLEVEL), SALARY, EDLEVEL, EMPNO 34 FROM EMPLOYEE ORDER BY 1 DESC Using the EMPLOYEE table, select the EMPNO column in integer form for further processing in the application. SELECT INTEGER(EMPNO) FROM EMPLOYEE Assume that the column BIRTHDATE (date) has an internal value equivalent to '1964-07-20'. INTEGER(BIRTHDATE) results in the value 19 640 720. Assume that the column STARTTIME (time) has an internal value equivalent to '12:03:04'. INTEGER(STARTTIME) results in the value 120 304. ====================================================================== ================================================= LENGTH scalar function >>-LENGTH--(--expression--)------------------------------------>< The schema is SYSIBM. The LENGTH function returns the length of a value. The argument can be an expression that returns a value of any built-in data type. 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 does not include the null indicator byte of column arguments that allow null values. The length of strings includes blanks but does not include the length control field of varying-length strings. 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 DBCS characters. The length of all other values is the number of bytes used to represent the value: 2 for small integer 4 for large integer (p/2)+1 for decimal numbers with precision p The length of the string for binary strings The length of the string for character strings 4 for single-precision floating-point 8 for double-precision floating-point 4 for date 3 for time 10 for timestamp 35 Examples: Assume the host variable ADDRESS is a varying length character string with a value of '895 Don Mills Road'. LENGTH(:ADDRESS) Returns the value 18. Assume that START_DATE is a column of type DATE. LENGTH(START_DATE) Returns the value 4. This example returns the value 10. LENGTH(CHAR(START_DATE, EUR)) ====================================================================== = LONG_VARCHAR scalar function >>-LONG_VARCHAR--(--character-string-expression--)------------->< The schema is SYSIBM. The LONG_VARCHAR function returns a LONG VARCHAR representation of a character string data type. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. character-string-expression An expression that returns a value that is a character string with a maximum length of 32 700 bytes. The result of the function is a LONG VARCHAR. If the argument can be null, the result can be null; if the argument is null, the result is the null value. ====================================================================== ======================================== LONG_VARGRAPHIC scalar function >>-LONG_VARGRAPHIC--(--graphic-expression--)------------------->< The schema is SYSIBM. The LONG_VARGRAPHIC function returns a LONG VARGRAPHIC representation of a double-byte character string. graphic-expression An expression that returns a value that is a graphic string with a maximum length of 16 350 double byte characters. The result of the function is a LONG VARGRAPHIC. If the argument can be null, the result can be null; if the argument is null, the result is the null value. 36 ====================================================================== ============================================== LTRIM scalar function >>-LTRIM--(--string-expression--)------------------------------>< The schema is SYSIBM. (The SYSFUN version of this function continues to be available with support for LONG VARCHAR and CLOB arguments.) The LTRIM function removes blanks from the beginning of string-expression. The argument can be a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type. If the argument is a graphic string in a DBCS or EUC database, then the leading double byte blanks are removed. If the argument is a graphic string in a Unicode database, then the leading UCS-2 blanks are removed. Otherwise, the leading single byte blanks are removed. The result data type of the function is: VARCHAR if the data type of string-expression is VARCHAR or CHAR VARGRAPHIC if the data type of string-expression is VARGRAPHIC or GRAPHIC The length parameter of the returned type is the same as the length parameter of the argument data type. The actual length of the result for character strings is the length of string-expression minus the number of bytes removed for blank characters. The actual length of the result for graphic strings is the length (in number of double byte characters) of string-expression minus the number of double byte blank characters removed. If all of the characters are removed, the result is an empty, varying-length string (length is zero). If the argument can be null, the result can be null; if the argument is null, the result is the null value. Example: Assume that host variable HELLO is defined as CHAR(9) and has a value of ' Hello'. VALUES LTRIM(:HELLO) The result is 'Hello'. ====================================================================== =============================
还剩35页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享pdf获得金币 ] 0 人已下载

下载pdf

pdf贡献者

open_hr

贡献于2014-02-09

下载需要 10 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf