Skip site navigation (1) Skip section navigation (2)

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

From: Cyrus Downey <cdowney(at)pryermachine(dot)com>
To: Brian Hurt <bhurt(at)janestcapital(dot)com>
Cc: Srinivas Iyyer <srini_iyyer_bio(at)yahoo(dot)com>,pgsql-novice(at)postgresql(dot)org
Subject: Re: problem with single quote : ' [VASCL:A111C2C2718]
Date: 2006-08-22 17:58:08
Message-ID: 44EB45B0.8040605@pryermachine.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

Responses

pgsql-novice by date

Next:From: Stephan SzaboDate: 2006-08-22 18:03:42
Subject: Re: Passing Arguments to a trigger function(Problem)
Previous:From: Brian HurtDate: 2006-08-22 17:52:31
Subject: Re: problem with single quote : '

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group