Message-ID: <41E5709D.8010705@onesystem.com>
Date: Wed, 12 Jan 2005 11:46:53 -0700
From: Edmund Bacon <ebacon@onesystem.com>
User-Agent: Mozilla Thunderbird 0.9 (X11/20041103)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: Kieran Ashley <krashley@space.qinetiq.com>
Subject: Re: Problems with Quotes
References: <6665151E3647D711B27B0090277C004F9AF721@ntexch02s.scs.dra.hmg.gb>
In-Reply-To: <6665151E3647D711B27B0090277C004F9AF721@ntexch02s.scs.dra.hmg.gb>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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