SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-19
Removing Leading or Trailing Characters From a
String
For fixed-length CHAR columns, the result is the length of the column. For columns
defined as VARCHAR, the result is the length of the string in the column. Consider this
example:
CREATE TABLE EMPLOYEE (LAST_NAME CHAR(20),
ADDRESS VARCHAR (100);
INSERT INTO EMPLOYEE VALUES ("ROBERT SMITH",
("19333 LEXINGTON PARKWAY") ;
CHAR_LENGTH (LAST_NAME)
CHAR_LENGTH (ADDRESS)
Because LAST_NAME is a fixed-length column of 20 characters, the result for the
CHAR_LENGTH function on LAST_NAME is 20, if LAST_NAME is not null. If
LAST_NAME is null, then CHAR_LENGTH is null.
Because ADDRESS is a variable-length column, the result for the CHAR_LENGTH
function on ADDRESS is the current length of the string. The length of the string in the
example is 23. If the address is updated to “12 BENTON PARK”, then the function
returns the updated string length of 14.
Any argument that is null returns a null value. If the argument is a host variable with a
null value or a null result of another function, the result is null. In the previous example,
if ADDRESS is null, a null value is returned.
Do not confuse a null value with a string that has a length of 0. A string with 0 length
returns a value of 0:
CHAR_LENGTH ("")
Removing Leading or Trailing Characters From a String
You can use the TRIM function to remove any of these from a character string:
•
Leading characters
•
Trailing characters
•
Both leading and trailing characters
The trim option you specify describes which of the three options you want. If you
specify no trim option, the default is both. You can provide the TRIM character you
want removed or use the default, which is a blank character.
TRIM (ADDRESS)
uses the default blank TRIM character, removes leading and trailing blank characters,
and implies this:
TRIM (BOTH " " FROM ADDRESS)