Received: from smtp016.mail.yahoo.com (smtp016.mail.yahoo.com [216.136.174.113]) by postgresql.org (8.11.3/8.11.4) with SMTP id f9IIf0P12612 for ; Thu, 18 Oct 2001 14:41:01 -0400 (EDT) (envelope-from brett_schwarz@yahoo.com) Received: from c1377995-a.bllvu1.wa.home.com (HELO thor) (65.4.166.93) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Oct 2001 18:40:54 -0000 X-Apparently-From: Date: Thu, 18 Oct 2001 11:30:23 -0700 From: Brett Schwarz To: Steve Frampton Cc: pgsql-sql@postgresql.org Subject: Re: Doing a regexp-based search/replace? Message-Id: <20011018113023.50ac0754.brett_schwarz@yahoo.com> In-Reply-To: References: X-Mailer: Sylpheed version 0.6.2 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200110/257 X-Sequence-Number: 5211 You could write a Tcl (i.e. pltcl) function, and use that to do what you want: CREATE FUNCTION remove(varchar) RETURNS varchar AS ' set input $1 regsub -- {-.*$} $input {} output return $output ' language 'pltcl'; [NOTE: untested] you may have to monkey with the regexp to get exactly what you want... --brett On Thu, 18 Oct 2001 12:03:28 -0400 (EDT) Steve Frampton wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hello: > > I've got a table containing property_id's with values of the form > ###-####. I would like to discard the slash onwards (and I can't use a > substr() because I am not guaranteed if a) the -#### portion exists, b) > what position it exists from. > > If this were a text file, I would use a sed expression such as: > > cat textfile | sed 's/-.*$//' > > I've been looking for a way to do this with PostgreSQL but so far haven't > found a function that seems to be suitable. I thought maybe I could do it > with translate, but translate doesn't appear to work with regular > expressions. So far I've tried things like: > > select translate(property_id, '-.*', '') from mytable; > > I need to do this, because the -.* portion of my property_id was entered > in error, and I would like to do an update on the entire table and just > have the left-hand side of the property_id column remaining. > > Any ideas? Thank you in advance. > > - ---------------< LINUX: The choice of a GNU generation. >------------- > Steve Frampton http://www.LinuxNinja.com > GNU Privacy Guard ID: D055EBC5 (see http://www.gnupg.org for details) > GNU-PG Fingerprint: EEFB F03D 29B6 07E8 AF73 EF6A 9A72 F1F5 D055 EBC5 > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.0.0 (GNU/Linux) > Comment: For info see http://www.gnupg.org > > iD8DBQE7zv1TmnLx9dBV68URAisEAJ4nNYz4lxpgWojULE/Xo9SUb5IexgCfS9At > J6kAVn/3vFHeJkl9bjr4AcQ= > =W4xQ > -----END PGP SIGNATURE----- > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com