Search for an Occurrence of a String Supplied with a Prompt Variable

To search a field for an occurrence of a string supplied through a prompt variable create a condition that uses the prompt variable input as the search string in a LOCATE function.

SELECT
 CUSTID,
 CONAME,
 FNAME,
 LNAME,
 CITY
FROM
 SCDB1.CSTHDR CSTHDR
WHERE
 LOCATE( ' ' || &coname, CONAME ) <> 0

This will return only those records where the searched field contains the string entered in the prompt reply wtih a leading space added. If the input is 'abb' it will return a record containing ' abbreviate' but not 'cabbie'.

Ref#: 1483622