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