From yudie@axiontech.com Mon Feb  17 20:47:16 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 83BD7475AD4
	for <pgsql-sql@postgresql.org>; Mon, 17 Feb 2003 15:47:16 -0500 (EST)
Received: from axiontech.com (unknown [216.52.172.74])
	by postgresql.org (Postfix) with SMTP id A82AB475A8D
	for <pgsql-sql@postgresql.org>; Mon, 17 Feb 2003 15:47:15 -0500 (EST)
Received: (qmail 20849 invoked from network); 17 Feb 2003 20:47:06 -0000
Received: from pc-00132 (HELO yudie) (192.168.1.132)
	by mail.axiontech.com (192.168.1.6) with SMTP;
	17 Feb 2003 20:47:05 -0000
Message-ID: <009c01c2d6c6$fb985b90$8401a8c0@yudie>
From: "Yudie" <yudie@axiontech.com>
To: <pgsql-sql@postgresql.org>
Subject: Format Function
Date: Mon, 17 Feb 2003 14:56:01 -0600
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0099_01C2D694.AFE1CD30"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/386
X-Sequence-Number: 11784

This is a multi-part message in MIME format.

------=_NextPart_000_0099_01C2D694.AFE1CD30
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,
Is there any default function for formating string in postgre sql?
for instance:
Format('123ABCDE', '####-###-###') =3D> '12-3AB-CDE'

The closest function I know is the to_char() function but it only works for=
 numbers

please advise

Thanks
Yude


------=_NextPart_000_0099_01C2D694.AFE1CD30
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4916.2300" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Is there any default function for formatin=
g string=20
in postgre sql?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>for instance:<BR>Format('123ABCDE', '####-=
###-###')=20
=3D&gt; '12-3AB-CDE'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>The closest function&nbsp;I know is the to=
_char()=20
function but it only works for numbers</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>please advise</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Yude</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;</DIV></FONT></BODY></HTML>

------=_NextPart_000_0099_01C2D694.AFE1CD30--





From josh@agliodbs.com Mon Feb  17 20:58:36 2003
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 <pgsql-sql@postgresql.org>; 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 <pgsql-sql@postgresql.org>; 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 <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Yudie" <yudie@axiontech.com>, <pgsql-sql@postgresql.org>
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




From yudie@axiontech.com Tue Feb  18 00:12:32 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0FBCB475B84
	for <pgsql-sql@postgresql.org>; Mon, 17 Feb 2003 19:12:32 -0500 (EST)
Received: from axiontech.com (unknown [216.52.172.74])
	by postgresql.org (Postfix) with SMTP id 78005475AFA
	for <pgsql-sql@postgresql.org>; Mon, 17 Feb 2003 19:12:29 -0500 (EST)
Received: (qmail 11094 invoked from network); 18 Feb 2003 00:12:32 -0000
Received: from pc-00132 (HELO yudie) (192.168.1.132)
	by mail.axiontech.com (192.168.1.6) with SMTP;
	18 Feb 2003 00:12:32 -0000
Message-ID: <014d01c2d6e3$aff25610$8401a8c0@yudie>
From: "Yudie" <yudie@axiontech.com>
To: <pgsql-sql@postgresql.org>
References: <009c01c2d6c6$fb985b90$8401a8c0@yudie>
	<200302171256.59403.josh@agliodbs.com>
Subject: Re: Format Function
Date: Mon, 17 Feb 2003 18:21:28 -0600
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/390
X-Sequence-Number: 11788

OK thanks for the answer.
I made my function with plpgsql to do formating,
It's not perfect but at least make it easy to do simple formating
==============================================
CREATE FUNCTION myformat(text,text) returns text as'
 DECLARE
  i integer;
  n integer;
  strtemp text;

 BEGIN
  i := length($2);
  n := length($1);
  strtemp='''';

  WHILE i > 0 AND n > 0 LOOP
   IF substr($2,i,1) = ''#'' THEN
    strtemp := substr($1,n,1) || strtemp;
    n := n - 1;
   ELSE
    strtemp := substr($2,i,1) || strtemp;
   END IF;

   i := i - 1;
  END LOOP;

  return strtemp;
 END
' language 'PLPGSQL'



----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Yudie" <yudie@axiontech.com>; <pgsql-sql@postgresql.org>
Sent: Monday, February 17, 2003 2:56 PM
Subject: Re: [SQL] Format Function


Yudie,

> Is there any default function for formating string in postgre sql?
> for instance:
> Format('123ABCDE', '####-###-###') => '12-3AB-CDE'
>
> The closest function I know is the to_char() function but it only works
for
numbers

No, there isn't.  You could write one, though.  For example, you could
write:

(7.3 syntax)

CREATE FUNCTION yudie_format(text) RETURNS text AS
'SELECT SUBSTR($1, 1, 4) || ''-'' || SUBSTR($1,5,3) || ''-'' ||
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
function in PL/Perl which would take a format mask and apply it to any text
string.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco