Handling a single quote in a SQL clause for use with vvOut_execSQLtoJSON

[mwmayer4]

When building the where clause for an SQL statement to use with vvOut_execSQLtoJSON()

What “advanced technique” might anyone recommend wherein a value contains a single quote?

For example: the value of lastName = HEBERT’S

and the sql_where is built as:

sql_where = ‘ where lastName like ‘
+ sq
+ %trim(lastName)
+ ‘%’
+ sq;

This causes an error to be returned.
VV00007 - SQL statement prep failure (SQLSTT=42601) - SQL0104 - Token % was not valid. Valid tokens:

because it looks like this when sent to the function:

like 'HEBERT'S%'

So what's the magic trick to allow the single quote in the like value?

[robert.swanson]

You need to escape the single quotes inside lastName. So try this:

sql_where = ' where lastName like '
              + sq
              + %trim(%scanRpl(sq : sq+sq : lastName))
              + '%'
              + sq;