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)
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’)
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)
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"