LOCATE: Returning the Position of a Substring in a String

How to:

Given a substring, a source string and a starting position (the default is 1), LOCATE returns the position of the first occurrence of the substring, starting the search at the starting position. If the substring is not found, LOCATE returns zero (0). The search is case insensitive.

Syntax: How to Return the Position of a Substring in a String 

LOCATE(substr, source [,start])

where:

substr

Alphanumeric

Is the search string.

source

Alphanumeric

Is the source string.

start

Numeric

Is the optional starting position for the search. If omitted, it defaults to 1.

Example: Returning the Position of a Substring in a String

The following SQL SELECT statement searches for the character a in FULLNAME, starting at position 3, and starting at position 1. 

SQL
SELECT FULLNAME,
LOCATE('a', FULLNAME, 3) AS 'START AT 3',
LOCATE('a', FULLNAME) AS 'START AT 1' 
FROM
WF_RETAIL_CUSTOMER T1
FETCH FIRST 5 ROWS ONLY;
TABLE 
HEADING CENTER
"Search for the Character 'a'"
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
TYPE=HEADING, STYLE=BOLD, SIZE=16, $
ENDSTYLE
END

The output is shown in the following image.