Demonstration of functions related to DB2 strings and their usage (1)

The following articles mainly describe the functions related to DB2 strings and their specific usage. The main content includes character conversion functions, space functions, and substring functions, description of string comparison functions and data type conversion functions.

I. character conversion functions

1. ASCII ()

Returns the ASCII value of the leftmost character of a character expression. In ASCII) functions, strings with only numbers do not need to be enclosed by '', But strings with other characters must be enclosed by''. Otherwise, an error occurs.

2. CHAR ()

Converts an ASCII code to a character. If no value is entered ~ The ASCII code value between 255, CHAR) returns NULL.

3. LOWER () and UPPER ()

LOWER () converts all strings to lowercase letters, and UPPER () converts all strings to uppercase letters.

4. STR ()

Converts numeric data to numeric data.

STR (<float_exdivssion> [, length [, <decimal>])

Length indicates the length of the returned string, and decimal indicates the number of decimal places returned. If no length is specified, the default length value is 10 and the default value of decimal is 0.

If length or decimal is a negative value, NULL is returned;

When length is less than the number of digits to the left of the decimal point, including the symbol bit), return length *;

Obey length first, and then take decimal;

When the number of digits of the returned string is less than length, spaces are filled on the left.

Ii. Space-removing Functions

1. LTRIM () removes spaces in the string header.

2. RTRIM () removes spaces at the end of the string.

Iii. substring Functions

1. left ()

LEFT (<character_exdivssion>, <integer_exdivssion>)

Returns character_exdivssion, which starts from integer_exdivssion at the left.

2. RIGHT ()

RIGHT (<character_exdivssion>, <integer_exdivssion>)

Returns character_exdivssion, which is a string of up to integer_exdivssion characters.

3. SUBSTRING ()

SUBSTRING (<exdivssion >,< starting _ position>, length)

Returns the length of the string starting from the start _ position Character on the left.

Iv. String comparison functions

1. CHARINDEX ()

Returns the starting position of a specified substring in a DB2 string.

CHARINDEX (<'substring _ exdivssion'>, <exdivssion>)

Here, substring _ exdivssion is the character expression to be searched for, and exdivssion can be a string or a column name expression. If no substring is found, the return value is 0.

This function cannot be used for TEXT and IMAGE data types.

2. PATINDEX ()

Returns the starting position of a specified substring in a string.

PATINDEX (<'% substring _ exdivssion %'>, <column _ name>) must have a percent sign "%" before and after the neutron string expression; otherwise, the return value is 0.

Unlike the CHARINDEX function, wildcards can be used in the child strings of the PATINDEX function, which can be used for CHAR, VARCHAR, and TEXT data types.