SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-15
Using String Functions
Null values are not the same as blanks. Two blanks can be compared and found equal,
while the equivalence of two null values is indeterminate. Similarly, null values are not
the same as zeros. Zeros can participate in arithmetic operations, while null values are
excluded from arithmetic.
To determine whether a column accepts null values, you can query the COLUMNS
catalog table, or you can invoke a table description in the SQL format. The COLUMNS
table contains descriptions of all columns of all tables registered in a catalog (as
recorded in the TABLES catalog table). The one-character NULLALLOWED column in
the COLUMNS table contains a Y if a null value is allowed and an N if a null value is
prohibited. For more information, see the SQL/MP Reference Manual.
Using String Functions
A function is a specialized routine that can be applied to data to return a result. You
can use functions in SQL statements to manipulate characters. For example, using a
string function within SQL, you can:
•
Extract part of a string
•
Search for a string within a string
•
Search for a string, disregarding its case
•
Determine the length of a string in either characters or bytes
•
Remove leading and trailing characters from a string
You can apply string functions to all character data types, including VARCHAR. You
can also apply them to CHAR and VARCHAR data types that have the UPSHIFT
function applied.
Character operands must all have comparable collations if they will be compared to
each other. The result of a string function contains the same character set as the
operands.
You can also concatenate the results of string functions. For more information, see
Using the Concatenation Operator on page 1-20.
Extracting Part of a String
You can use the SUBSTRING function to extract any part of a string. You do this by
providing the string, the starting position for the extraction, and an optional length for
the result. The starting position is represented by a count of the number of characters
from the beginning of the string. The result of the SUBSTRING function is a character
expression called a substring.
Consider these examples:
SUBSTRING ("ROBERT JOHN SMITH" FROM 8 FOR 4)
SUBSTRING ("ROBERT JOHN SMITH" FROM 8)
SUBSTRING ("ROBERT JOHN SMITH" FROM 1 FOR 17)