SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-20
Using the Concatenation Operator
The next example uses an asterisk as a TRIM character and removes leading
asterisks from the value in the ADDRESS column:
TRIM (LEADING "*" FROM ADDRESS)
This example removes trailing blank characters from the value in the LAST_NAME
column:
TRIM (TRAILING " " FROM LAST_NAME)
The resulting string is always VARCHAR. For example, a CHAR or VARCHAR returns
a VARCHAR. An UPSHIFT CHAR or VARCHAR returns an UPSHIFT VARCHAR with
the same collating and character set attributes as those of the source character string.
The TRIM character and the character string to be trimmed must have comparable
collations and identical character sets.
TRIM can be useful with the concatenation operator. For an example, see Using the
Concatenation Operator next. You can also use TRIM to do LIKE comparisons with
fixed-length host variables. See Using LIKE With TRIM on page 1-35.
Using the Concatenation Operator
Using the concatenation operator (||), you can concatenate two strings to generate a
single string result, as in this example:
"ROBERT " || "SMITH"
which results in:
"ROBERT SMITH"
If either of the character strings is VARCHAR, then the concatenated result is
VARCHAR. If both character strings are CHAR, then the concatenated result is CHAR.
The collating sequence attribute of the concatenated string is determined by the rules
you use to determine the collating sequence of a comparison predicate. For further
information, see “Comparison Predicate” in the SQL/MP Reference Manual.
The concatenation operator is useful in combination with the TRIM function. Consider
this table:
CREATE TABLE NAMES (FIRST_NAME CHAR(15), LAST_NAME CHAR (15));
INSERT INTO NAMES VALUES ("ROBERT", "SMITH");
You can use the concatenation operator to retrieve the full name:
FIRST_NAME || LAST_NAME
This is the result:
"ROBERT SMITH "