Functions Defined by the SQL Standard

CHAR_LENGTH, OCTET_LENGTH, BIT_LENGTH Functions

>POSITION Function

POSITION(str IN str2)

This function searches for a string or text str in the string or text str2. The return value is zero if the string was not found, or the first position of the occurrence of str in str2 beginning with one.

If str is an empty string, the return value will always be 1. If str2 is an empty string, the return value will always be 0.

This function is not case-sensitive if str2 is a string or text that ignores letter case.

Variations from Intermediate to Full

• This function belongs to the SQL2 standard.

602SQL variations

• 602SQL does not distinguish an empty value or NULL value in string and text types. Therefore, if the first argument is NULL, the result will be 1. If the second argument is NULL, the result will be 0.

Example:

A function that returns the position of a first blank space in a parameter.

FUNCTION FIRSTBLANK (strpar CHAR (100)) RETURNS INT;
BEGIN
  RETURN (POSITION(' ' IN strpar));
END

See

• CHAR_LENGTH
• SUBSTRING

Functions Defined by the SQL Standard

CHAR_LENGTH, OCTET_LENGTH, BIT_LENGTH Functions