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