8.5.9 TRIM

(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

To Page Top

(2) Explanation of specification format

erase-rule:

Specifies the rules for deleting characters. If this specification is omitted, BOTH is assumed.

LEADING:

If LEADING is specified, it checks whether the characters match the deletion characters in order from the beginning of the string, and if they match, deletes the characters. If a character different from the deleted character appears, the process ends there.

The following is an example of the execution result when LEADING is specified.

Example:

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: Δ: Half-width space

TRAILING:

If TRAILING is specified, it checks whether the characters match the deletion characters in order from the end of the string, and if they match, deletes the characters. If a character different from the deleted character appears, the process ends there.

The following is an example of the execution result when TRAILING is specified.

Example:

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: Δ: Half-width space

BOTH:

If BOTH is specified, it checks whether the characters match the deletion characters in order from both the beginning and end of the string, and if they match, deletes the characters. If a character different from the deleted character appears, the process ends there.

The following is an example of the execution result when BOTH is specified.

Example:

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: Δ: Half-width space

erase-character:

Specifies the characters to delete from the target data.

The following specification rules apply:

• Specify erase-character in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify CHAR, VARCHAR, or STRING type data for erase-character.

• If erase-character is omitted, its assumed value is a half-width space character.

• If a dynamic parameter is specified by itself as erase-character, the assumed data type of the dynamic parameter will be VARCHAR(32000).

target-data:

Specifies the data to be deleted for the characters specified for erase-character.

The following specification rules apply:

• Specify the target data in the form of a value expression. For details about value expressions, see 7.21 Value expression.

• Specify CHAR, VARCHAR, or STRING type data for the target data.

• You cannot specify a dynamic parameter by itself for the target data.

To Page Top

(3) Rules

1. 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)
   STRING	STRING

   Legend: n: Maximum length of the target data

2. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

3. If target-data or chars-to-remove is the null value, the execution result will be the null value.

4. 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.

5. If all the target character string data is removed, the execution result will be data whose actual length is 0 bytes.

6. 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.

To Page Top

(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"

To Page Top