SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-187
TRIM Function
TRIM Function
The TRIM function removes the specified characters from the left side, the right side,
or both sides of a character string.
trim-type
specifies whether characters are to be trimmed from the left side (LEADING), right
side (TRAILING), or both sides (BOTH) of srcstr. If you omit the trim-type,
the default is BOTH.
trim_chrs
is the character or characters to be removed from the srcstr. The default is the
space character.
srcstr
is a SQL character value expression from which spaces or trim characters are
removed. See Character Value Expressions on page 6-41.
Considerations for TRIM
Result of TRIM
The result is always of type NON ANSI VARCHAR, with maximum length equal to the
fixed length or maximum variable length of srcstr. If the source character string is an
upshifts CHAR or VARCHAR string, the result is an upshifts VARCHAR type.
Examples of TRIM
•
Return 'Robert':
TRIM (' Robert ')
•
The EMPLOYEE table defines FIRST_NAME as CHAR(15) and LAST_NAME as
CHAR(20). This expression uses the TRIM function to return the value 'Robert
Smith' without extra blanks:
TRIM (first_name) || ' ' || TRIM (last_name)
•
In this example, characters '1' and '0' are removed from both sides of the colA
value:
create table tab1(colA varchar(20), colB int);
insert into tab1 values('0101010101ten0101010101', 10);
TRIM ([[trim-type] [trim-chrs] FROM] srcstr)
trim-type is:
LEADING | TRAILING | BOTH