pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFormat Function
3+ messages / 2 participants
[nested] [flat]
* Format Function
@ 2003-02-17 20:56 Yudie <yudie@axiontech.com>
2003-02-17 20:56 ` Re: Format Function Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 3+ messages in thread
From: Yudie @ 2003-02-17 20:56 UTC (permalink / raw)
To: pgsql-sql
Hi,
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
please advise
Thanks
Yude
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Format Function
2003-02-17 20:56 Format Function Yudie <yudie@axiontech.com>
@ 2003-02-17 20:56 ` Josh Berkus <josh@agliodbs.com>
2003-02-18 00:21 ` Re: Format Function Yudie <yudie@axiontech.com>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-02-17 20:56 UTC (permalink / raw)
To: Yudie <yudie@axiontech.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Format Function
2003-02-17 20:56 Format Function Yudie <yudie@axiontech.com>
2003-02-17 20:56 ` Re: Format Function Josh Berkus <josh@agliodbs.com>
@ 2003-02-18 00:21 ` Yudie <yudie@axiontech.com>
0 siblings, 0 replies; 3+ messages in thread
From: Yudie @ 2003-02-18 00:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-02-18 00:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-17 20:56 Format Function Yudie <yudie@axiontech.com>
2003-02-17 20:56 ` Josh Berkus <josh@agliodbs.com>
2003-02-18 00:21 ` Yudie <yudie@axiontech.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox