DB2 Aggregate functions

VALUE Function

 

Syntax: VALUE (EXPRESSION1, EXPRESSION2)

 

The VALUE function returns a non-empty VALUE. When the first parameter is not empty, the VALUE of this parameter is directly returned. If the first parameter is empty, returns the value of the first parameter.

 

 

COALESCE Function

 

Syntax: COALESCE (ARG1, ARG2 ...)

 

COALESCE returns the first non-null parameter in the parameter set. Usage is similar to the VALUE function.

 

 

LENGTH Function

 

Syntax: LENGTH (ARG)

 

The LENGTH function returns the LENGTH of a parameter.

 

 

 

LCASE and LOWER functions

 

Syntax: LCASE (), LOWER ()

 

The LCASE and LOWER functions return the LOWER-case form of a fixed-length and variable-length string.

 

 

 

 

UCASE and UPPER Functions

 

Syntax: UCASE (), UPPER ()

 

The UCASE and UPPER functions return the UPPER case of a fixed-length or variable-length string.

 

 

LTRIM, RTRIM Functions

 

Syntax: LTRIM (), RTRIM ()

 

The LTRIM and RTRIM functions remove spaces on the left or right from CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC.

 

 

 

 

LEFT and RIGHT Functions

 

Syntax: LEFT (ARG, LENGTH), RIGHT (ARG, LENGTH)

 

The LEFT and RIGHT functions return the leftmost and rightmost LENGTH strings of ARG. ARG can be a CHAR or binary string.

 

 

 

 

CONCAT Functions

 

Syntax: CONCAT (ARG1, ARG2)

 

The CONCAT function returns the connection between two strings.

 

 

 

 

INSERT Function

 

Syntax: INSERT (ARG1, POS, SIZE, ARG2)

 

The INSERT function returns a string that removes the SIZE of ARG1 from the POS and inserts ARG2 into the position.

 

 

 

 

LOCATE Function

 

Syntax: LOCATE (ARG1, ARG2, <POS>)

 

The LOCATE function searches for the first position of ARG1 in ARG2. If the POS is specified, the first position of ARG1 appears in the POS of ARG2.

 

 

 

POSSTR Function

 

Syntax: POSSTR (EXP1, EXP2)

 

The POSSTR function returns the position of EXP2 in EXP1.

 

 

 

REPEAT Function

 

Syntax: REPEAT (ARG1, NUM_TIMES)

 

The REPEAT function returns the string that ARG1 is repeated for NUM_TIMES.

 

 

 

REPLACE Function

 

Syntax: REPLACE (EXP1, EXP2, EXP3)

 

The REPLACE function uses EXP3 to REPLACE all exp2.

 

 

 

SPACE Function

 

Syntax: SPACE (SIZE)

 

The SPACE function returns a string containing the size space.

 

 

 

SUBSTR Function

 

Syntax: SUBSTR (ARG1, POS, <LENGTH>)

 

The SUBSTR function returns the LENGTH starting from the POS position in ARG1. If the LENGTH is not specified, the remaining characters are returned.

 

 

 

 

Select column1 | columnsfrom tablename with ur;

 

(

Link column1 and columns2. (the data types of the two columns must be the same ):

For example, column1 = 'abc'

Column2 = 'xyz'

The execution result of this statement is abcxyz.

)