8.5.9 TRIM
Removes instances of the specified characters from the target character string. The characters can be removed in any of the following ways:
-
Remove the specified characters starting from the beginning of the character string.
-
Remove the specified characters starting from the end of the character string.
-
Remove characters starting from both the beginning and the end of the character string.
- Organization of this subsection
(1) Specification format
scalar-function-TRIM ::= TRIM([{where chars-to-remove |where |chars-to-remove} FROM] target-data) where ::= {LEADING|TRAILING|BOTH} chars-to-remove ::= value-expression target-data ::= value-expression
(2) Explanation of specification format
- where:
-
Specifies where to begin the process of removing characters. If this is omitted, BOTH is assumed.
- LEADING:
-
When LEADING is specified, it removes all characters that match any of the characters specified for removal, proceeding from the beginning of the character string, and stopping as soon as it encounters a character that is not targeted for removal.
The following examples illustrate the execution results when LEADING is specified.
Examples
TRIM(LEADING '012' FROM '1020rst201') → 'rst201'
TRIM(LEADING 'a' FROM 'aaaadatabaseaaaa') → 'databaseaaaa'
TRIM(LEADING 'abc' FROM 'aabbccdatabase') → 'database'
TRIM(LEADING FROM 'databaseΔ') → 'databaseΔ'
TRIM(LEADING '012' FROM 'database') → 'database'
Legend:
Δ: Single-byte space
- TRAILING:
-
When TRAILING is specified, it removes all characters that match any of the characters specified for removal, proceeding from the end of the character string, and stopping as soon as it encounters a character that is not targeted for removal.
The following examples illustrate the execution results when TRAILING is specified.
Examples
TRIM(TRAILING '012' FROM '1020rst201') → '1020rst'
TRIM(TRAILING 'a' FROM 'aaaadatabaseaaaa') → 'aaaadatabase'
TRIM(TRAILING 'abes' FROM 'aabbccdatabase') → 'aabbccdat'
TRIM(TRAILING FROM 'databaseΔ') → 'database'
TRIM(TRAILING '012' FROM 'database') → 'database'
Legend:
Δ: Single-byte space
- BOTH:
-
When BOTH is specified, it removes all characters that match any of the characters specified for removal, proceeding from both the beginning and end of the character string, stopping as soon as it encounters a character that is not targeted for removal.
The following examples illustrate the execution results when BOTH is specified.
Examples
TRIM(BOTH '012' FROM '1020r212st201') → 'r212st'
TRIM(BOTH 'a' FROM 'aaaadatabaseaaaa') → 'database'
TRIM(BOTH 'abces' FROM 'aabbccdatabase') → 'dat'
TRIM(BOTH FROM 'databaseΔ') → 'database'
TRIM(BOTH '012' FROM 'database') → 'database'
Legend:
Δ: Single-byte space
- chars-to-remove:
-
Specifies the characters to be removed from the target data.
The following rules apply:
-
Specify chars-to-remove in the form of a value expression. Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
You must specify CHAR or VARCHAR type data for chars-to-remove.
-
If chars-to-remove is omitted, its value is assumed to be a space character.
-
If a dynamic parameter is specified by itself for chars-to-remove, the assumed data type of the dynamic parameter is VARCHAR(32000).
-
- target-data:
-
Specifies the data from which the characters specified in chars-to-remove are to be removed.
The following rules apply:
-
Specify the target data in the form of a value expression. For details about value expressions, see 7.20 Value expression.
-
Specify CHAR or VARCHAR type data for the target data.
-
You cannot specify a dynamic parameter by itself for the target data.
-
(3) Rules
-
The data type and data length of the execution result are shown in the following table.
Table 8‒20: Data type and data length of the execution result of the scalar function TRIM Data type and data length of the target data
Data type and data length of the execution result
CHAR(n)
VARCHAR(n)
VARCHAR(n)
Legend: n: Maximum length of the target data
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If target-data or chars-to-remove is the null value, the execution result will be the null value.
-
If the actual length of the target data is 0 bytes or 0 characters, the execution result will be data whose actual length is 0 bytes.
-
If all the target character string data is removed, the execution result will be data whose actual length is 0 bytes.
-
If you specify data whose actual length is 0 bytes or 0 characters for chars-to-remove, the execution result will be the target data.
(4) Example
- Example:
-
Remove the numeric prefix and suffix from the character string data in column C2 of table T1.
SELECT "C1",TRIM(BOTH '0123456789' FROM "C2") FROM "T1"