Functions › Scalar Functions › Character Functions

Character Functions

Following is the syntax diagram for character-level scalar functions:

►►─┬─┬─ LOWER ─────┬─ (expression) ─┬─────────────────────────────────────────►◄
   │ └─ LOWERCASE ─┘                │
   ├─┬─ UPPER ─────┬─ (expression) ─┤
   │ └─ UPPERCASE ─┘                │
   ├─ SQUEEZE(expression) ──────────┤
   ├─ LTRIM(expression) ────────────┤
   ├─ RTRIM(expression) ────────────┤
   ├─ TRIM(trim_operands) ──────────┤
   └─ STRIP(strip_operands) ────────┘

LOWER(expression) or LOWERCASE(expression)

This character function returns a copy of the result of the input expression that has been converted to lowercase characters. The input expression must resolve to the character data type and cannot consist of bit, mixed, or Katakana data. Bit and mixed data are rejected. Katakana data is processed, producing an unpredictable result string. The data type, length, and nullability of the result matches that of the input.

Following is an example of using the LOWER scalar function:

 SELECT LOWER(last_name) FROM customer_names

Given input of

' Smith '

Resulting output is

' smith '

UPPER(expression) or UPPERCASE(expression)

This character function returns a copy of the result of the input expression that has been converted to uppercase characters. The input expression must resolve to the character data type and cannot consist of bit, mixed, or Katakana data. Bit and mixed data are rejected. Katakana data is processed, producing an unpredictable result string. The data type, length, and nullability of the result matches that of the input.

Following is an example of using the UPPER scalar function:

 SELECT UPPER(last_name) FROM customer_names

Given input of

' Smith '

Resulting output is

' SMITH '

SQUEEZE(expression)

This character function returns a copy of the result of the input expression that has had both leading and trailing white space (blanks, nulls, new lines (line feeds), carriage returns, horizontal tabs and form feeds (vertical tabs)) removed and has had any embedded white space converted to blanks. The input expression must resolve to the variable-length character (VARCHAR) data type and cannot consist of bit or mixed data. Katakana strings are processed as if they were EBCDIC. The data type and nullability of the result matches that of the input. The length of the result is the squeezed length.

Following is an example of using the SQUEEZE scalar function:

 SELECT SQUEEZE(last_name) FROM customer_names

Given input of

' Smith '

Resulting output is

'Smith'

LTRIM(expression)

This character function returns a copy of the result of the input expression that has had leading blanks removed. The input expression must resolve to the character data type and may not consist of bit or mixed data. Katakana strings are processed as if they were EBCDIC. The result is VARCHAR with nullability matching that of the input. The length of the result is the trimmed (shortened) length.

Following is an example of using the LTRIM scalar function:

 SELECT LTRIM(last_name) FROM customer_names

Given input of

' Smith '

Resulting output is

'Smith '

RTRIM(expression)

This character function returns a copy of the result of the input expression that has had trailing blanks removed. The input expression must resolve to the character data type and cannot consist of bit or mixed data. Katakana strings are processed as if they were EBCDIC. The result is VARCHAR with nullability matching that of the input. The length of the result is the trimmed (shortened) length.

Following is an example of using the RTRIM scalar function:

 SELECT RTRIM(last_name) FROM customer_names

Given input of

' Smith '

Resulting output is

' Smith'

TRIM(trim_operands)

The TRIM character function removes extraneous characters from the start and/or end of a character string. The trim-source_expression (see following) must resolve to a CHAR, VARCHAR, or GRAPHIC data type and must be compatible with the data type of the trim-char_expression as shown in the following description. Katakana strings are processed as if they were EBCDIC. For CHAR and VARCHAR input, the result is VARCHAR. For GRAPHIC input, the result is VARGRAPHIC. Nullability of the result matches that of the input. The length of the result is the trimmed (shortened) length.

►►─┬─────────────────────────────────────────┬─ trim-source_expression ───────►◄
   └─┬─────────┬─┬─────────────────┬ ─ FROM ─┘
     └─ l_t_b ─┘ └─ trim-char_exp ─┘

l_t_b

This parameter specifies where to remove unwanted characters.

Specify LEADING or L if you want to remove unwanted characters from the start of the input source string.

Specify TRAILING or T if you want to remove unwanted characters from the end of the input source string.

Specify BOTH or B if you want to remove unwanted characters from both the start and the end of the input source string. The default is BOTH.

trim-char_exp

This trim character expression parameter specifies the character to be removed. The default is the blank character.

trim-source_expression

This parameter supplies the input string to be operated upon.

Following is an example of using the TRIM function:

 SELECT TRIM(BOTH ' ' FROM last_name) FROM customer_names

Given input of

' Smith '

Resulting output is

'Smith'

STRIP(strip_operands)

The STRIP character function, provided for DB2 syntax compatibility, operates identically to the TRIM function described above in that STRIP removes extraneous characters from the start and/or end of a character string. For details, see the previously given TRIM description.

►►─ strip-source_expression ─┬──────────────────────────────────┬─────────────►◄
                             └─ , l_t_b ─┬────────────────────┬─┘
                                         └─ , strip-char_exp ─┘

strip-source_expression

This parameter supplies the input string to be operated upon.

l_t_b

This parameter specifies where to remove unwanted characters.

Specify LEADING or L if you want to remove unwanted characters from the start of the input source string.

Specify TRAILING or T if you want to remove unwanted characters from the end of the input source string.

Specify BOTH or B if you want to remove unwanted characters from both the start and the end of the input source string. The default is BOTH.

, strip-char_exp

This strip character expression parameter specifies the character to be removed. The default is the blank character.

Following is an example of using the STRIP function:

 SELECT STRIP(last_name, BOTH, ' ') FROM customer_names

Given input of

' Smith '

Resulting output is

'Smith'