Thread: 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 BerkusAglio Database SolutionsSan Francisco

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 BerkusAglio Database SolutionsSan Francisco