Re: problem with single quote : '

Srinivas Iyyer wrote:

>Dear group,
> In one of my tables, the varchar data includes
>single-quotes.
>
>Example:
>3',5'-cyclic-nucleotide phosphodiesterase
>
>I populated tables by introducing \ in front of '
>
>Example:
>3\',5\'-cyclic-nucleotide phosphodiesterase
>
>
>I am using R to query and analyze the data and it is
>turning out to be a problem to get a character vector
>in R to have the output like this:
>
>EXECUTE count_fterm_sql('3\',5\'-cyclic-nucleotide
>phosphodiesterase activity');
>
>somehow it always provides me
>EXECUTE count_fterm_sql('3\\',5\\'-cyclic-nucleotide
>phosphodiesterase activity');
>
>The above query with two \\ does not work in postgres.
>
>
>I tried a variety of tricks in R to get to what I want
>that suits postgres. No luck.
>
>so I decided to substitute where ever I find instances
>of ' (i.e: 3', 10', esterase's etc) to "prime".
>
>Eg: 3prime, 10prime, esterasesprimes.
>
>I have all my data in table gobe. I dont know how many
>rows have ' character.
>
>how can I substitute ' with prime in the whole table.
>
>BTW, is this a good way to solve the problem. For
>instance bioinformatics people who are dealing with GO
>terms, how are they approaching this problem.
>
>
>

A better solution might be to use $_$ instead of ' to quote strings. If
you use $_$, you don't need to escape '.

# select $_$Foo'bar$_$;
?column?
----------
Foo'bar
(1 row)

#

Note that you can use any sequence of alphabetical characters or
underscores to do quotes- so
$foobar$$_$'$_$$foobar$
is the quoted string $_$'$_$.

Brian