Обсуждение: problem with single quote : '

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.

PS: Dear Sean, what is your opinion.  did you ever
come across this problem.  Do you have any solution in
R.

thanks


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

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
>

Thank you all for your help.



-----

mydb=# EXECUTE
fetch_count_fterm_sql('3'',5''-cyclic-nucleotide
phosphodiesterase activity');
 count
-------
    17
(1 row)

This particular option solved my problem. It was easy
to manipulate in R (no problem of \  at all )

----


This was more crude way I was working before.

mydb=# EXECUTE
fetch_count_fterm_sql('3\',5\'-cyclic-nucleotide
phosphodiesterase activity');
 count
-------
    17
(1 row)




Thanks again for immediate help.


--- Cyrus Downey <cdowney@pryermachine.com> wrote:

> 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
> >
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com