X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2C341DB9B8 for ; Thu, 17 Nov 2005 21:10:24 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93680-06 for ; Fri, 18 Nov 2005 01:10:26 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sapo.pt (relay4.ptmail.sapo.pt [212.55.154.24]) by svr1.postgresql.org (Postfix) with SMTP id 54190DB9AD for ; Thu, 17 Nov 2005 21:10:13 -0400 (AST) Received: (qmail 2352 invoked by uid 0); 18 Nov 2005 01:10:17 -0000 Received: from unknown (HELO sapo.pt) (10.134.35.158) by relay4 with SMTP; 18 Nov 2005 01:10:17 -0000 Received: (qmail 317 invoked from network); 18 Nov 2005 01:10:17 -0000 X-AntiVirus: PTMail-AV 0.3.87.1 X-Virus-Status: Clean (0.00400 seconds) Received: from unknown (HELO hmv02) (hmv@[82.155.145.71]) (envelope-sender ) by mta8 (qmail-ldap-1.03) with SMTP for ; 18 Nov 2005 01:10:17 -0000 Message-ID: <06c001c5ebdc$d6d4cd10$580bfea9@hmv02> From: =?iso-8859-1?Q?H=E9lder_M._Vieira?= To: References: <1132269492.30942.20.camel@dev11dt.corp.localmatters.com> <5d001d303f001ea0847254c3f0a352d9@squeegeemedia.com> <437D1862.2060402@PresiNET.com> Subject: Re: how to do a find and replace Date: Fri, 18 Nov 2005 01:10:17 -0000 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2670 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.919 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.919 X-Spam-Level: * X-Archive-Number: 200511/143 X-Sequence-Number: 23394 > update media_instance set location=replace(location,'v.','') > where location like '%/0/v.%' For more complex search/replace operations, 8.1 provides an interesting function, named 'regexp_replace'. It probably goes unnoticed because although there is a small reference in the manual in section 'E.1.3.6. Data Type and Function Changes' (http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1) and a description in section '9.7.3 POSIX Regular expressions' (http://www.postgresql.org/docs/8.1/interactive/functions-matching.html), it isn't mentioned in section '9.4. String Functions and Operators' (http://www.postgresql.org/docs/8.1/interactive/functions-string.html). Hélder M. Vieira