SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-17
Searching for a String Within a String
Substring Results That Are Null
If the character string, the starting position, or the substring length is a null value, the
result is null.
Substring Results That Are Empty Strings
Sometimes a SUBSTRING function returns a result that is an empty string. An empty
string is a string with a length of 0 (“”), which is not the same as a null value.
These examples of SUBSTRING functions return empty strings:
•
The sum of the starting position and the substring length is less than 1:
SUBSTRING ("ROBERT JOHN SMITH" FROM -5 FOR 3)
The sum of -5 and 3 is -2. The resulting substring length is 0.
•
The starting position is greater than the length of the character string:
SUBSTRING ("ROBERT JOHN SMITH" FROM 19 FOR 3)
The starting position is 19, but the string length is only 17.
•
The length for the extracted substring is 0:
SUBSTRING ("ROBERT JOHN SMITH" FROM 8 FOR 0)
Substring Errors
An error occurs if either of these conditions is violated:
•
If the substring is not part of a dynamically prepared statement, the data types of
the starting position and the substring length each must be an exact numeric value
with a scale of 0. (If the substring is part of a dynamically prepared statement, the
data type is processed as if it were numeric (x,0)).
In this example, if colb is a numeric column with the value 5.3, a compilation error
occurs:
SUBSTRING ("ROBERT JOHN SMITH" FROM 2 FOR colb)
•
The sum of the starting position and the substring length can be negative if the
substring length is not explicitly negative. Because the substring length in this
example is an explicit -3, the query returns an error:
SELECT SUBSTRING (A FROM 2 FOR -3) FROM TABLE ;
Searching for a String Within a String
The POSITION function searches for a given substring in a character string and
returns the starting character position of that substring. In this example, the result is 6:
POSITION ("JANE" IN "MARY JANE MASTERS")