"Go" DB2 database functions Daquan

DB2 built-in functions are really a lot, it is almost impossible to master each function, so this article is not intended to describe the specific use of each function, but rather to provide an overview of the functions of each function, so that when you feel you need certain functions of the function, then learn their specific usage is not too late.

The DB2 built-in functions are broadly divided into the following categories:

Aggregate function  type conversion function  mathematical function  string function  date time function  XML function  partition function  security function  Other

Let's look at what the functions of each class are and how they function.

One: aggregation function

Value function Syntax: Value (expression1,expression2) The value function returns the value of a non-null value when its first argument is not NULL, and returns the first parameter if the first argument is null. COALESCE function Syntax: COALESCE (ARG1,ARG2 ...) COALESCE returns the first non-null parameter in a parameter set. Usage is similar to the value function. The length function syntax: the lengths (ARG) function returns the size of the parameter. LCASE, LOWER function Syntax: LCASE (), LOWER () LCASE, LOWER function returns the lowercase form of a fixed-length, variable-length string. UCASE, UPPER function Syntax: UCASE (), UPPER () UCASE, UPPER functions return the uppercase form of a fixed-length, variable-length string. LTRIM, RTRIM function Syntax: LTRIM (), RTRIM () LTRIM, RTRIM functions remove left or right spaces from char, VARCHAR, graphic, or vargraphic. The left and right function syntax: Arg,length, Arg,length (), and the back function returns the leftmost, rightmost LENGTH string of ARG, which can be a char or binary string. CONCAT function Syntax: the CONCAT (ARG1,ARG2) CONCAT function returns a connection of two strings. The Insert function syntax: the Insert (ARG1,POS,SIZE,ARG2) Insert function returns a string that removes the SIZE ARG1 from the POS and inserts ARG2 into that position. LOCATE function Syntax: the LOCATE (arg1,arg2,<pos>) LOCATE function finds the first occurrence of ARG2 in ARG1, and if POS is specified, finds the first occurrence of ARG2 from the POS at ARG1. posstr function Syntax: the POSSTR (EXP1,EXP2) Posstr function returns the position of EXP2 in EXP1. REPEAT function Syntax: the REPEAT (arg1,num_times) REPEAT function returns a string that ARG1 is repeated num_times times. Replace function syntax: replace (EXP1,EXP2,EXP3) Replace function replaces all EXP2 in EXP1 with EXP3. Space function Syntax: space(SIZE) The space function returns a string containing a space of size. SUBSTR function Syntax: the SUBSTR (arg1,pos,<length>) SUBSTR function returns the length of the POS position in ARG1, and returns the remaining characters if length is not specified.

Two: type conversion function

DB2 provides the appropriate functions for each data type, in general, the mutual conversion between them is very simple, see the following table: function  function Description  SMALLINT    Returns the value of SMALLINT type  integer returns an integer The value of type BIGINT returns the value of type BIGINT decimal returns the value of type decimal,  Real    Returns the value of the real type  double  returns double The value of type float returns a value of type float, CHAR returns a value of type CHARACTER, and  varchar Returns a varchar type of values  Varchar_format_  the bit formats the bit-character sequence as a VARCHAR-type return Varchar_bit_format returns the  formatted post-character sequence back to the format before  Long_varchar returns a    LONG The value of the VARCHAR type CLOB returns the value of the CLOB type GRAPHIC returns the value of the  GRAPHIC type  vargraphic  returns the value  of the vargraphic type Long_vargraphic returns a value of type LONG vargraphic Dbclob Returns a value of type Dbclob the value of the BLOB  type returned    by  DATE    The value of the DATE type time returns a value of type  TIMESTAMP   Returns a value of type TIMESTAMP

Three: Mathematical functions

function  function Description  abs,absval  Returns the absolute value of the parameter    if the argument is greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0  RAND    returns the random floating-point number between 0 and 1  MOD to find remainder  ROUND   Returns the rounding value starting at the position of parameter 2 at the right of the decimal point of parameter 1,  TRUNCATE OR TRUNC   truncates from the right of the decimal point of the expression and returns the value floor   Returns the largest integer less than or equal to the parameter  CEILING or ceil returns the smallest integer value greater than or equal to the parameter  power returns the parameter   1 of the argument 2 powers  SQRT returns the    square root  of the parameter DIGITS  Returns a string representing the absolute value of the parameter  Multiply_alt the    product of the returned parameter  DEGREES the angle  RADIANS the degree to the radian  sin sine function  SINH    hyperbolic sine function  ASIN inverse    chord function  cos cosine function  COSH    hyperbolic cosine function ACOS cosine    function  TAN Tangent function  TANH    hyperbolic tangent function  ATAN inverse tangent function  atanh hyperbola inverse tangent function  ATAN2   inverse tangent function  COT cotangent function LN returns the natural logarithm of  The parameter log returns the natural logarithm  of the parameter  LOG10   returns the natural logarithm based on 10  

Four: String functions

function  function Description  ASCII convert   character to ASCII CHR convert  ASCII code to character  STRIP   Delete string start and end whitespace character or other specified character  TRIM    Removes whitespace characters at the beginning and end of a string or other specified characters  LTRIM The   beginning of the deletion string  RTRIM   The white space character of the trailing string  LCASE or LOWER  Returns a string of lowercase  UCASE OR UPPER returns  the uppercase SUBSTR of the string  SUBSTRING    returns the substring left Returns the n character of the  start   right returns the end of the n character  POSITION returns the position of the    first occurrence of parameter 2 in Parameter 1  posstr  Returns the position of the first occurrence of parameter 2 in Parameter 1  LOCATE  Returns the position of the first occurrence of parameter 2 in Parameter 1  space   Returns the length specified by the parameter, including the string  REPEAT  string  back to parameter 1 after repeating argument 2 times CONCAT  connecting two strings  INSERT  adds a string to the specified string replace  replace string  TRANSLATE   replaces one or more characters in a string with another character  character_length    Returns the length of the string  octet_length    Returns the number of bytes of the string  ENCRYPT the string encryption  Decrypt_bin and Decrypt_chars   decryption of encrypted data  gethint return password hint  

Five: Date Time function　

function  Function Description The year part of the return date of month month part of the return date day part of the date HOUR returns the    Hour portion of the date  MINUTE  returns the minute part  of a date SECOND returns the second part of the date microsecond the microsecond portion of the returned date MONTHNAME the month name of the returned date  dayname the day of the week  that returns the date QUARTER return the specified date is the quarter  WEEK    returns the current date is the first week of the year, starting from Sunday  Week_iso    Returns the current date is the week of the year, starting  from Monday every week DAYOFWEEK   Return the current date is the day of the week, Sunday is 1  dayofweek_iso   Returns the current date is the day of the week, and Monday is 1  dayofyear   Return the current date is the day of the year days    returns the time represented by an integer, used to find the time interval  julian_day  returned from January 1, 4712 B.C (JULIAN date calendar) Days to a specified date  midnight_seconds    Returns the number of seconds from midnight to a specified time  timestampdiff   returns the interval of two TIMESTAMP dates  Timestamp_ ISO   returns  the date of the timestamp type To_char the string representation of the returned date  varchar_format format the  date as a string to_date convert the  string to a date  Timestamp_format    

VI: XML functions

Using XML in DB2 SQL functions

Seven: partition function

function  function Description  datapartitionnum    Returns the serial number of the data partition  Dbpartitionnum the partition number of the  returned row  Hashedvalue the return row of the Distribution Map Index (0 to 4095)  

Eight: Security functions　　

function  function Description  Seclabel    Returns the unnamed security label  Seclabel_by_name    returns the specific security label  Seclabel_to_char    Returns all elements of a label  

IX: Other

function  function Description  coalesce converts null to another value value   converts null to another value  nullif  If two parameters are equal, returns NULL, otherwise, Returns the first parameter  HEX returns a value of 16 for the length of the returned  value  table_name returns the  TABLE name  table_schema    Returns the schema name  type_id The return data type represents  type_name return   data type name  Type_schema return  the schema name DEREF   Returns an instance of the parameter type  identity_val_local  Returns the last value assigned to the identity column  rec2xml returns the XML tag format string containing the column name and column data  event_mon_state Returns the operation status of an event monitor  Raise_error throws an error, you can specify SQLState and Error_message, a bit like Java throws an exception  

　　

　

"Go" DB2 database functions Daquan