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) ────────────────────────────────────┤ ├─ POSSTR(posstr_operands) ───────────────────────────────────┤ └─ REPLACE(replace_operands) ─────────────────────────────────┘
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
' Smith '
' smith '
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
' Smith '
' SMITH '
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
' Smith '
'Smith'
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
' Smith '
'Smith '
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
' Smith '
' Smith'
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 ─┘
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.
This trim character expression parameter specifies the character to be removed. The default is the blank character.
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
' Smith '
'Smith'
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 ─┘
This parameter supplies the input string to be operated upon.
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.
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
' Smith '
'Smith'
The POSSTR character function returns the starting position of the first occurrence of the search_string within the source_string. The search is case-sensitive.
If the search_string is not found and neither argument is null, the result is 0. If the search_string is found, the result is a number from 1 to the actual length of the source_string. If either parameter is NULL, the result is NULL. The parameters must be CHAR or VARCHAR.
The posstr_operands are as follows:
►►─ POSSTR(source_string, search_string) ───────────────────────►◄
This parameter specifies the input string to be searched.
This parameter specifies the string for which to search in the source_string.
Following is an example of using the POSSTR function:
SELECT POSSTR(last_name, 'th') FROM customer_names
'Smith '
4
The REPLACE character function replaces all occurrences of the search_string within the source_string with the replacement_string.The is function is case-sensitive.
The result is always VARCHAR with a maximum length of 4000 if the length of the source_string is less than 4000, or 32720 if the source_string is longer than 4000.
If any parameter is NULL, the result will be NULL. If the replacement_string is empty, the search_string is deleted.
If the replacement_string is too long to fit in the source_string, an SQLCODE -320 error is issued with the message: REPLACE FUNCTION RESULT STRING IS TOO LONG. The parameters must be CHAR or VARCHAR. The replace_operands are as follows:
►►─ REPLACE(source_string, search_string, replacement string) ───────────►◄
This parameter specifies the input string to be searched.
This parameter specifies the string expression for which to search in the source_string.
This parameter specifies the string expression that replaces the search_string.
Following is an example of using the REPLACE function:
SELECT REPLACE(last_name, 'Sm', 'Ke') FROM customer_names
'Smith '
'Keith '
|
Copyright © 2014 CA.
All rights reserved.
|
|