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