Re: Search using embedded SQL on field containing quote

On 11-Sep-2014 15:11 -0500, (WalzCraft) Jerry Forss wrote:

I have and inquiry pgm (SQLRPGLE) that searches on names. Works
great until the search name contains a quote like O'CONNER.

The select statement looks like:

Select PhCsNo, PhCsNm, PhCont, PhCTyp, PhPhNo, PhFxNo, PhEml,
PhAdd1, PhAdd2, PhCity, PhStCd, PhZipC
From PhMast PM
Where PM.PhCont Like '%O'CONNER%'
Order By PhCsNo

How should it look when there is an embedded quote?

The answer for the SQL [unrelated to the RPG] was already given; i.e. to escape the apostrophe with an apostrophe:
LIKE '%O''CONNER%'

An alternative is to use the replace-one-character character of the underscore, although possibly giving undesirable matches [even while resolving any issue whereby the presumed-as apostrophe was stored as something other than the apostrophe; e.g. the ` character]; i.e.:
LIKE '%O_CONNER%'

Depending on how the search value is obtained [i.e. the example shows a literal used for the operand on the LIKE predicate, but may not be representative.?] and where the value is utilized in a host language [such as RPG], the following might be helpful to review; the first for embedded SQL, the second for purely dynamic SQL:

Given: SrchTkn is declared as an alpha Host Variable /varying/ [of sufficient length]:

1) No escaping the apostrophe [as string delimiter] is required:

... WHERE PM.PhCont LIKE '%' CONCAT :SrchTkn CONCAT '%'

2) Escaping the apostrophe [as string delimiter] is handled by the SQL scalar REPLACE() function, before appending the result to the dynamic clause being constructed:

exec SQL
SET :SrchTkn = REPLACE( :SrchTkn , '''' , '''''' ) ;

WhereClause = ' WHERE PM.PhCont LIKE ''%'' CONCAT '
+ SrchTkn + 'CONCAT ''%''' ;