X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2EA2C475BF9 for ; Mon, 17 Feb 2003 15:58:36 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id DEEE2475AFF for ; Mon, 17 Feb 2003 15:57:34 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2844099; Mon, 17 Feb 2003 12:57:42 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "Yudie" , Subject: Re: Format Function Date: Mon, 17 Feb 2003 12:56:59 -0800 X-Mailer: KMail [version 1.4] References: <009c01c2d6c6$fb985b90$8401a8c0@yudie> In-Reply-To: <009c01c2d6c6$fb985b90$8401a8c0@yudie> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302171256.59403.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/387 X-Sequence-Number: 11785 Yudie, > Is there any default function for formating string in postgre sql? > for instance: > Format('123ABCDE', '####-###-###') =3D> '12-3AB-CDE' >=20 > The closest function I know is the to_char() function but it only works f= or=20 numbers No, there isn't. You could write one, though. For example, you could writ= e: (7.3 syntax) CREATE FUNCTION yudie_format(text) RETURNS text AS=20 'SELECT SUBSTR($1, 1, 4) || ''-'' || SUBSTR($1,5,3) || ''-'' ||=20 SUBSTR($1,9,4); ' LANGUAGE SQL IMMUTABLE STRICT; As a simple formatting function. For that matter, it would be the work of a weekend for someone to write a= =20 function in PL/Perl which would take a format mask and apply it to any text= =20 string. --=20 -Josh Berkus Aglio Database Solutions San Francisco