NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
T-12
Considerations—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.
Considerations—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
•
The following example returns “Robert”:
TRIM (" Robert ")
•
The following example uses a table created as follows:
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
as follows:
TRIM (TRAILING " " FROM FIRST_NAME) || " " || TRIM
(LAST_NAME)
For more information about the concatenation operator (||), see Character
Expressions on page C-11.
•
You can also use the TRIM function to perform a LIKE comparison with fixed-
length host variables, as follows:
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