DB2 common functions (1)

The following articles mainly describe the detailed analysis of DB2 functions, as well as the precautions for these common DB2 functions in actual operations and the description of their actual application code, the following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

1): string functions

Keywords: db2, function, string

Recently, when the project is preparing to migrate to DB2, it has to report the online tutorials and visualization of ms SQL. Here, we will record the usage of common DB2 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.

SQL code

Eg:

-- Indicates that if T1.ID is null, an empty string is returned. If T1.ID is not empty, T1.ID is returned.

Select value (ID, '') FROM T1

Eg:

-- Indicates that if T1.ID is null, an empty string is returned. If T1.ID is not empty, T1.ID is returned.

Select value (ID, '') FROM T1

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.

SQL code

Eg:

Select length (NAME) FROM T1

Eg:

Select length (NAME) FROM T1

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.

SQL code

Eg:

Select lcase (NAME), LOWER (NAME) FROM T1

Eg:

Select lcase (NAME), LOWER (NAME) FROM T1

UCASE and UPPER Functions

Syntax: UCASE (), UPPER ()

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

SQL code

Eg:

Select ucase (NAME), UPPER (NAME) FROM T1

Eg:

Select ucase (NAME), UPPER (NAME) FROM T1

LTRIM, RTRIM Functions

Syntax: LTRIM (), RTRIM ()

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

SQL code

Eg:

Select ltrim (NAME), RTRIM (NAME) FROM T1

Eg:

Select ltrim (NAME), RTRIM (NAME) FROM T1

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.

SQL code

Eg:

Select left (NAME, 2), RIGHT (NAME, 2) FROM T1

Eg:

Select left (NAME, 2), RIGHT (NAME, 2) FROM T1

CONCAT Functions

Syntax: CONCAT (ARG1, ARG2)

The CONCAT function returns the connection between two strings.