Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

TRIM

The TRIM function trims leading or trailing characters (or both) from a character string.

SQL syntax

There are four valid syntax options for TRIM:

Parameters

TRIM has the parameters:

Parameter Description
TRIM (

LEADING | TRAILING | BOTH

[Trim_character]

FROM

Expression )

LEADING | TRAILING| BOTH are qualifiers to TRIM function. LEADING removes all leading instances of Trim_character from Expression. TRAILING removes all trailing instances of Trim_character from Expression. BOTH removes leading and trailing instances of Trim_character from Expression.

Trim_character is optional. If specified, it represents the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column used for trimming Expression. Must be only one character. If you do not specify Trim_character, it defaults to a single blank. If Trim_character is a character literal, then enclose it in single quotes.

FROM is required.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

TRIM (

Trim_character

FROM

Expression )

Removes both leading and trailing instances of Trim_character from Expression.

Trim_character is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column used for trimming Expression. Must be only one character. If Trim_character is a character literal, then enclose it in single quotes.

FROM must follow Trim_character. Assumes LEADING | TRAILING | BOTH qualifiers have not been specified.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

TRIM (

Expression )

If you specify Expression (without a qualifier or Trim_character), then leading and trailing blank spaces are removed from Expression.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.


Description

Examples

Use TRIM function with qualifier to remove Trim_character '0' from Expression '0000TRIM Example0000':

Command> SELECT TRIM (LEADING '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example0000 >
1 row found.
Command> SELECT TRIM (TRAILING '0' FROM '0000TRIM Example0000') FROM dual;
< 0000TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with qualifier to remove blank spaces. Do not specify a Trim_character. Default value for Trim_character is blank space:

Command> SELECT TRIM (LEADING FROM '    TRIM Example    ') FROM dual;
< TRIM Example     >
1 row found.
Command> SELECT TRIM (TRAILING FROM '    TRIM Example    ') FROM dual;
<     TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH FROM '    TRIM Example    ') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with Trim_character '0'. Do not specify a qualifier. Leading and trailing '0's are removed from Expression '0000TRIM Example0000':

Command> SELECT TRIM ('0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function without a qualifier or Trim_character. Leading and trailing spaces are removed.

< TRIM Example >
1 row found.
Command> SELECT TRIM ('    TRIM Example    ') FROM dual;