DB2 common functions in detail (a): String functions

value Function

Syntax: VALUE (Expression1,expression2)

The value function is returned with a non-null value, and when its first argument is not NULL, it returns the values of the parameter directly, and if the first argument is null, the value of the first parameter is returned.

eg:--indicates that if T1.id is empty, an empty string is returned and T1.id is returned if the t1.id is not empty. SELECT VALUE (ID, ') from T1

COALESCE function

Syntax: COALESCE (ARG1,ARG2 ...)

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


Length function

Syntax: LENGTH (ARG)

The length function returns the lengths of the arguments.

eg
SELECT LENGTH (NAME) from T1

LCASE, lower functions

Syntax: LCASE (), LOWER ()

The LCASE, lower function returns the lowercase form of a fixed-length, variable-length string.

eg
SELECT LCASE (name), LOWER (name) from T1

UCASE, Upper functions

Syntax: UCASE (), UPPER ()

The UCASE, upper function returns the uppercase form of a fixed-length, variable-length string.

eg
SELECT UCASE (name), UPPER (name) from T1

LTRIM, RTrim functions

Syntax: LTRIM (), RTRIM ()

The LTRIM, RTrim function removes the left or right spaces from char, VARCHAR, graphic, or vargraphic.

eg
SELECT LTRIM (name), RTRIM (name) from T1

Left, right function

Syntax: Left (arg,length), right (Arg,length)

The left and right functions return the leftmost, rightmost length string of arg, which can be a char or binary string.

eg
SELECT Left (name,2), right (name,2) from T1

concat function

Syntax: CONCAT (ARG1,ARG2)

The CONCAT function returns a connection of two strings.

eg
SELECT CONCAT (first_name,last_name) from T1

Insert function

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

The Insert function returns a string that ARG1 the size character from the POS and inserts ARG2 into the position.

eg

Locate function

Syntax: LOCATE (arg1,arg2,<pos>)

The locate function finds where ARG1 first appears in ARG2, and if POS is specified, finds the first occurrence of ARG1 from the POS at ARG2.

eg
SELECT LOCATE (' A ', NAME) from T1

Posstr function

Syntax: Posstr (EXP1,EXP2)

The POSSTR function returns the position of EXP2 in EXP1.

eg
SELECT LOCATE (NAME, ' a ') from T1

Repeat function

Syntax: REPEAT (arg1,num_times)

The Repeat function returns a string that ARG1 is repeated num_times times.

eg
SELECT REPEAT (name,2) from T1

Replace function

Syntax: REPLACE (EXP1,EXP2,EXP3)

The Replace function replaces all EXP2 in the EXP1 with EXP3.

eg
SELECT REPLACE (' romannd ', ' NND ', ' CCB ') from T1

Space function

Syntax: SPACE (SIZE)

The space function returns a string containing a space of size.

eg
SELECT SPACE (Ten) from T1

substr function

Syntax: SUBSTR (arg1,pos,<length>)

The SUBSTR function returns the length character at the beginning of the POS position in ARG1, and returns the remaining characters if no length is specified.

eg
SELECT SUBSTR (' CDNJFDJFJD ', 5,2) from T1

DB2 common functions in detail (a): String functions