--- In ib-support@y..., "Rob Schuff" <rob@b...> wrote:

> Folks,
>
> the following e-mail came in to me from a colleague. Does anyone

have an

> answer for this?
>
> The SQL standard specifies that single quotes are to be used around

string

> literals (<fieldname>='<value>') in SQL statements. <...>
>
> Does anyone know of a way to handle saving (with SQL UPDATE or

INSERT) of

> strings that contain internal apostrophes (single quotes),

preserving the

> single quotes within the string?

The SQL standard says you should use two consecutive single quotes for
each single quote that you want retained as part of the string value.

For example, [in honour of St. Patrick's Day?]

update countries
set capital = 'O''ttawa'
where country = 'Canada'

select capital ...

O'ttawa


For reference, see SQL92 section 5.3 <literal>, especially Syntax Rule
9:

"<quote symbol> ::= <quote><quote>
<blah blah blah ...>
Each <quote symbol> contained in <character string literal> represents
a single <quote> in both the value and the length of the <character
string literal>. The two <quote>s contained in a <quote symbol> shall
not be separated by any <separator>."

Cheers,
db