SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-18
Searching for a String Without Regard for its Case
You can optionally specify which occurrence of the substring you are seeking; for
example, you can specify the first occurrence or the third. The data type of the
occurrence is unsigned numeric with a scale of 0. The result for this search is 5:
POSITION ("IS" IN "MISSISSIPPI", 2)
If no substring is found, the function returns 0. If you omit occurrence, then the function
returns the first occurrence of the substring.
Searching for a String Without Regard for its Case
Using the UPSHIFT function, you can ignore the case of a string when searching for a
value:
SELECT * FROM EMPLOYEE
WHERE UPSHIFT(LAST_NAME) = "SMITH" ;
The example results in a list of all employees whose last names are Smith. The value
“SMITH” in the LAST_NAME column can be uppercase, lowercase, or a combination
of cases, and it will be found.
You can also use a collation to ignore case, but performance could be better when you
use the UPSHIFT function.
Determining the Length of a String
You can use the OCTET_LENGTH function to obtain the number of bytes in a
character string. You can use the CHARACTER_LENGTH function, abbreviated
CHAR_LENGTH, to obtain the number of characters in a character string.
For multibyte characters, such as Kanji, the OCTET_LENGTH and CHAR_LENGTH
functions return results that differ from each other.
OCTET_LENGTH (_KANJI "abcdef")
returns the value 6, but this example returns the value 3:
CHAR_LENGTH (_KANJI "abcdef")
For single-byte characters, the results returned by OCTET_LENGTH and
CHAR_LENGTH are the same. The data type of the result for single-byte and for
multibyte characters for both functions is a 2-byte signed integer with a scale of 0.
When the OCTET_LENGTH or the CHAR_LENGTH functions are applied to a string
literal, such as “ROBERT”, the result is the length of the string. But when these
functions are applied to a column, the value depends on the definition of the column.