SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
T-12
Consideration—TRIM Function
trim-char
specifies the character to be trimmed from the string. The data type of trim-char
must be CHARACTER with a maximum length of 1. If you omit trim-char, SQL
trims blanks (“ “) from the string.
character-string
specifies the string from which to trim characters.
Consideration—TRIM Function
The result is always of type VARCHAR, with the same collating attributes as the source
character-string. If the source character string is an upshifted CHAR or
VARCHAR string, the result is an upshifted VARCHAR type. The TRIM character and
the character string to be trimmed should have the same or comparable collations and
identical character sets. Otherwise, SQL returns an error.
Examples—TRIM Function

This example returns “Robert”:
TRIM (" Robert ")

This example uses a table created:
CREATE TABLE NAMES (FIRST_NAME CHAR(15), LAST_NAME CHAR(15))
INSERT INTO NAMES VALUES ("Robert", "Smith")

To retrieve “Robert Smith” without extra blanks, you could use the TRIM operator:
TRIM (TRAILING " " FROM FIRST_NAME) || " " || TRIM
(LAST_NAME)
For more information about the concatenation operator (||), see Character
Expressions on page C-14.

You can also use the TRIM function to perform a LIKE comparison with
fixed-length host variables:
CREATE TABLE T (A CHAR(10))
INSERT INTO T VALUES ("ROBERT")
INSERT INTO T VALUES ("ROMEO")
INSERT INTO T VALUES ("JOHN")
SELECT A FROM T WHERE TRIM(A) LIKE :host_var