Mainframe: DB2 LOCATE Built-in Function-Best examples

DB2+MAINFRAME+Jobs+Career+Apply NowYou already enjoyed with my earlier post on built-in functions. LOCATE is a Scalar function. Syntax for LOCATE function is as follows:

SELECT LOCATE(‘String to be searched’, Source string’, Start position)
FROM SYSIBM.SYSDUMMY1;

Key points in LOCATE function

  • The LOCATE function returns the starting position of search-string within source-string.
  • If search-string is not found and neither argument is null, the result is zero.
  • If search-string is found, the result is a number from 1 to the actual length of source-string.
  • If search-string has a length of zero, the result returned by the function is 1.
  • If the optional start is specified, it indicates the character position in source-string at which the search is to begin.
  • An optional string unit can be specified to indicate in what units the start and result of the function are expressed.

 Best examples of LOCATE:

SELECT LOCATE(‘N’, ‘DINING’)
FROM SYSIBM.SYSDUMMY1;

Result is : 3


Below examples is just to select columns, such that  word ‘GOOD’ to be present in NOTE_TEXT.

SELECT RECEIVED, SUBJECT, LOCATE(‘GOOD’, NOTE_TEXT)
FROM IN_TRAY
WHERE LOCATE(‘GOOD’, NOTE_TEXT) <> 0;

Using SET commands

SET :LOCATION = LOCATE('ß','Jürgen lives on Hegelstraße',1,CODEUNITS32); Result is "26"
SET :LOCATION = LOCATE('ß','Jürgen lives on Hegelstraße',1,OCTETS); REsult is "27"
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.