DB2 Scalar functions - POSSTR


Volta a página anterior

Volta ao Menu das scalar functions

Volta ao Menu Principal


Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

POSSTR

The POSSTR function returns the starting position of the first occurrence of one string (called the search-string) within another string (called the source-string).

POSSTR(source-string, search-string)

The schema is SYSIBM.

Numbers for the search-string position start at 1 (not 0).

The result of the function is a large integer.
If either of the arguments can be null, the result can be null; if either of the arguments is null, the result is the null value.

source-string
An expression that specifies the string to be searched through.
This expression must return a built-in character string, numeric value, Boolean value, or datetime value.
If the value is not a character string, it is implicitly cast to VARCHAR before the function is evaluated.

The expression can be specified by any of the following elements:

  • A constant
  • A special register
  • A global variable
  • A host variable (including a locator variable or a file reference variable)
  • A scalar function
  • A large object locator
  • A column name
  • An expression that concatenates (using CONCAT or ||) any of the previously listed items
search-string
An expression that specifies the string that is to be searched for.
This expression must return a value that is a built-in string, signed numeric, Boolean, or datetime value.
If the value is not a string, it is implicitly cast to VARCHAR before the function is evaluated.
The actual length must not exceed the maximum length of a VARCHAR.

The expression can be specified by any of the following elements:

  • A constant
  • A special register
  • A global variable
  • A host variable
  • A scalar function whose operands are any of the previously listed items
  • An expression that concatenates (using CONCAT or ||) any of the previously listed items
  • An SQL procedure parameter
The expression cannot be a LOB file reference variable.

The following strings or string expressions are invalid:
  • SQL user-defined function parameters
  • Trigger transition variables
  • Local variables in compound SQL (inlined) statements

In a Unicode database, if one argument is character (not FOR BIT DATA) and the other argument is graphic, then the search-string is converted to the data type of the source-string for processing.
If one argument is character FOR BIT DATA, the other argument must not be graphic (SQLSTATE 42846).

Both search-string and source-string have zero or more contiguous positions.
If the strings are character or binary strings, a position is a byte.
If the strings are graphic strings, a position is a double byte.
POSSTR operates on a strict byte-count basis, without awareness of either the database collation or changes between single and multi-byte characters.
The POSITION, LOCATE, or LOCATE_IN_STRING functions can be used to operate with awareness of the database collation and the string units.

The following rules apply:

  • The data types of source-string and search-string must be compatible, otherwise an error is raised (SQLSTATE 42884).
    • If source-string is a character string, then search-string must be a character string, but not a CLOB, with an actual length of 32672 bytes or less.
    • If source-string is a graphic string, then search-string must be a graphic string, but not a DBCLOB, with an actual length of 16336 double-byte characters or less.
    • If source-string is a binary string, then search-string must be a binary string with an actual length of 32672 bytes or less.
  • If search-string has a length of zero, the result returned by the function is 1.
  • Otherwise:
    • If source-string has a length of zero, the result returned by the function is zero.
    • Otherwise:
      • If the value of search-string is equal to an identical length substring of contiguous positions from the value of source-string, then the result returned by the function is the starting position of the first such substring within the source-string value.
      • Otherwise, the result returned by the function is 0.

Example

Select RECEIVED and SUBJECT columns as well as the starting position of the words 'GOOD BEER' within the NOTE_TEXT column for all entries in the IN_TRAY table that contain these words. 

   SELECT RECEIVED
   ,      SUBJECT
   ,      POSSTR(NOTE_TEXT, 'GOOD BEER')
   FROM   IN_TRAY
   WHERE  POSSTR(NOTE_TEXT, 'GOOD BEER') <> 0


© Copyright IBM Corp.