Re: problem with single quote : ' [VASCL:A111C2C2718]

Srinivas ,

Here is the help topic with covers it well.

http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-CONSTANTS

good luck

Brian Hurt wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>