Thread: Problems with Quotes

Problems with Quotes

From

Kieran Ashley

Date:

12 January 2005, 21:02:31

Hi,

I have a PL/SQL function which breaks up a comma-separated list of values stored in one column, and uses that (along
withother data) to make a new table.
 

My problem is that some of the incoming data is quoted e.g.

"value1, value2, value3"

Meaning that when I split on the commas, I end up with:

"value1
value2
value3"

I've tried using the replace() function to get rid of the ", but I can't figure out how to use it without throwing an
error. I tried
 

replace(col_name, '\"', '')

and several other permutations but to no avail, do I need to use something like an ASCII character code in order to get
ridof a quote?  If so which one, and if not, is there a better solution?
 

Many thanks.

Kieran

#############################################################

The information contained in this email and any subsequent
correspondence is private and is intended solely for the 
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any 
action taken or omitted to be taken in reliance on such 
information is prohibited and may be unlawful.

#############################################################

Re: Problems with Quotes

From

John DeSoi

Date:

12 January 2005, 21:17:43

On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:

> I've tried using the replace() function to get rid of the ", but I 
> can't figure out how to use it without throwing an error.  I tried
>
> replace(col_name, '\"', '')
>
> and several other permutations but to no avail, do I need to use 
> something like an ASCII character code in order to get rid of a quote? 
>  If so which one, and if not, is there a better solution?
>


Try '"' as in

select replace('this "is" it', '"', '');  replace
------------ this is it
(1 row)


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Re: Problems with Quotes

From

Kieran Ashley

Date:

12 January 2005, 21:23:08

I tried that.  It starts spitting out the rest of the script to STDIN until it gets to the next " (which is being used
toquote a table name about 100 lines further on" at which point it throws an error, and dies.

It seems it really wants me to escape it somehow, but neither '\"' or ''"' seems to work.

-----Original Message-----
From: John DeSoi [mailto:desoi@pgedit.com] 
Sent: 12 January 2005 18:18
To: Kieran Ashley
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Problems with Quotes

On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:

> I've tried using the replace() function to get rid of the ", but I 
> can't figure out how to use it without throwing an error.  I tried
>
> replace(col_name, '\"', '')
>
> and several other permutations but to no avail, do I need to use 
> something like an ASCII character code in order to get rid of a quote? 
>  If so which one, and if not, is there a better solution?
>

Try '"' as in

select replace('this "is" it', '"', '');  replace
------------ this is it
(1 row)

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

#############################################################

The information contained in this email and any subsequent
correspondence is private and is intended solely for the 
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any 
action taken or omitted to be taken in reliance on such 
information is prohibited and may be unlawful.

#############################################################

Re: Problems with Quotes

From

Edmund Bacon

Date:

12 January 2005, 21:47:06

Kieran Ashley wrote:
> I tried that.  It starts spitting out the rest of the script to STDIN until it gets to the next " (which is being
usedto quote a table name about 100 lines further on" at which point it throws an error, and dies.
 
> 
> It seems it really wants me to escape it somehow, but neither '\"' or ''"' seems to work.
> 
>
Perhaps you are forgetting to double up on your quote chars?

THis seems to work for me:

e.g.

test=# create or replace function bar(text)
test-# returns text
test-# language 'plpgsql'
test-# as 'begin return replace($1, ''"'', ''''); end;';
CREATE FUNCTION
test=# select bar('hello');  bar
------- hello
(1 row)

test=# select bar('hello "world"');     bar
------------- hello world
(1 row)

test=#


> -----Original Message-----
> From: John DeSoi [mailto:desoi@pgedit.com] 
> Sent: 12 January 2005 18:18
> To: Kieran Ashley
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Problems with Quotes
> 
> 
> On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
> 
> 
>>I've tried using the replace() function to get rid of the ", but I 
>>can't figure out how to use it without throwing an error.  I tried
>>
>>replace(col_name, '\"', '')
>>
>>and several other permutations but to no avail, do I need to use 
>>something like an ASCII character code in order to get rid of a quote? 
>> If so which one, and if not, is there a better solution?
>>
> 
> 
> 
> Try '"' as in
> 
> select replace('this "is" it', '"', '');
>    replace
> ------------
>   this is it
> (1 row)
> 
> 
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
> 
> #############################################################
> 
> The information contained in this email and any subsequent
> correspondence is private and is intended solely for the 
> intended recipient(s). For those other than the intended
> recipient(s) any disclosure, copying, distribution, or any 
> action taken or omitted to be taken in reliance on such 
> information is prohibited and may be unlawful.
> 
> #############################################################
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

-- 
Edmund Bacon <ebacon@onesystem.com>

Re: Problems with Quotes

From

Kieran Ashley

Date:

12 January 2005, 21:54:53

Ah!  Fantastic.

Thank you so much.   I'm still not entirely sure _why_ that works, but it does... so I can go home now!  ;)

Thanks again!


-----Original Message-----
From: Edmund Bacon [mailto:ebacon@onesystem.com] 
Sent: 12 January 2005 18:47
To: pgsql-sql@postgresql.org
Cc: Kieran Ashley
Subject: Re: [SQL] Problems with Quotes

Kieran Ashley wrote:
> I tried that.  It starts spitting out the rest of the script to STDIN until it gets to the next " (which is being
usedto quote a table name about 100 lines further on" at which point it throws an error, and dies.
 
> 
> It seems it really wants me to escape it somehow, but neither '\"' or ''"' seems to work.
> 
>
Perhaps you are forgetting to double up on your quote chars?

THis seems to work for me:

e.g.

test=# create or replace function bar(text)
test-# returns text
test-# language 'plpgsql'
test-# as 'begin return replace($1, ''"'', ''''); end;';
CREATE FUNCTION
test=# select bar('hello');  bar
------- hello
(1 row)

test=# select bar('hello "world"');     bar
------------- hello world
(1 row)

test=#


> -----Original Message-----
> From: John DeSoi [mailto:desoi@pgedit.com] 
> Sent: 12 January 2005 18:18
> To: Kieran Ashley
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Problems with Quotes
> 
> 
> On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
> 
> 
>>I've tried using the replace() function to get rid of the ", but I 
>>can't figure out how to use it without throwing an error.  I tried
>>
>>replace(col_name, '\"', '')
>>
>>and several other permutations but to no avail, do I need to use 
>>something like an ASCII character code in order to get rid of a quote? 
>> If so which one, and if not, is there a better solution?
>>
> 
> 
> 
> Try '"' as in
> 
> select replace('this "is" it', '"', '');
>    replace
> ------------
>   this is it
> (1 row)
> 
> 
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
> 
> #############################################################
> 
> The information contained in this email and any subsequent
> correspondence is private and is intended solely for the 
> intended recipient(s). For those other than the intended
> recipient(s) any disclosure, copying, distribution, or any 
> action taken or omitted to be taken in reliance on such 
> information is prohibited and may be unlawful.
> 
> #############################################################
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

-- 
Edmund Bacon <ebacon@onesystem.com>

#############################################################

The information contained in this email and any subsequent
correspondence is private and is intended solely for the 
intended recipient(s). For those other than the intended
recipient(s) any disclosure, copying, distribution, or any 
action taken or omitted to be taken in reliance on such 
information is prohibited and may be unlawful.

#############################################################

Re: Problems with Quotes

From

Michael Fuhr

Date:

12 January 2005, 21:59:09

On Wed, Jan 12, 2005 at 11:46:53AM -0700, Edmund Bacon wrote:

> Perhaps you are forgetting to double up on your quote chars?

If that's the problem then 8.0's dollar quoting will simplify
the situation:

CREATE OR REPLACE FUNCTION foo(text) RETURNS text AS $$
SELECT replace($1, '"', '');
$$ LANGUAGE sql;

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Problems with Quotes

From

John DeSoi

Date:

12 January 2005, 22:40:33

On Jan 12, 2005, at 2:00 PM, Kieran Ashley wrote:

> I'm still not entirely sure _why_ that works, but it does... so I can 
> go home now!  ;)

You should look at section 37.2.1 in the current docs. 8.0 has a new 
dollar quoting feature which makes this easier to deal with.

http://www.postgresql.org/docs/7.4/static/plpgsql-development-tips.html

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL