How to Use DB2 LOCATE Built-in Function

Checkout here my earlier post on built-in functions. LOCATE is a Scalar function. Here is my ideas how to use correctly.

Below is the Syntax for LOCATE function

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 Example of LOCATE Function

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"

Keep Reading