SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-16
Extracting Part of a String
In the first example, the extracted string starts from the eighth position of the original
string, “ROBERT JOHN SMITH”, and extends for four characters. “JOHN” is the result.
In the second example, the extracted string starts from the eighth position of the
original string and extends until the end. “JOHN SMITH” is the result.
The substring in the third example is the whole string. “ROBERT JOHN SMITH” is the
result.
If the sum of the starting position and the substring length is greater than the length of
the original character string, the substring from the start position to the end of the string
is returned. In this example, the sum of 8 and 15 is 23, which is longer than the 17-
character string:
SUBSTRING ("ROBERT JOHN SMITH" FROM 8 FOR 15)
Therefore, the string “JOHN SMITH” is returned.
If you do not specify a substring length, the result is the original string, beginning with
the specified start position and continuing through the end of the original string. “RT
JOHN SMITH” is the result in this example:
SUBSTRING ("ROBERT JOHN SMITH" FROM 5)
If the starting position is negative, the negative positions and position 0 are counted as
one character each but do not show up in the resulting string:
SUBSTRING ("ROBERT JOHN SMITH" FROM -1 FOR 5)
Positions -1 and 0 are counted as the first two positions. “ROB” is the resulting string.
Using a Substring in a Query
This example shows a SUBSTRING function used in a query:
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE SUBSTRING (FIRST_NAME FROM 1 FOR 4) = "MARY" ;
A list of all employees whose first names start with “MARY” is the result.
Data Types for Substring Results
A FIXED CHAR or VARCHAR data type returns a VARCHAR data type, and an
UPSHIFT CHAR or VARCHAR data type returns an UPSHIFT VARCHAR data type
with the same collating attributes as those of the source character string.
This example returns “ROBERT” with a collating sequence of FRENCH:
SUBSTRING ("ROBERT JOHN SMITH" COLLATE FRENCH FROM 1 FOR 6)