Re: [SQL] Format Function

[Josh Berkus]

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


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL]

[Tomasz Myrta]

George wrote:

OK this really is becoming one of the most frustrating experiences with 
windows  I have had. I have followed the instructions from differenet 
sites and user groups and still no luck. I am stuck at getting the 
ipc-daemon & service going. I have not found it on my machine. I am 
using 1.13-2 version of cygipc.
What's the problem? I have the same cygipc library and postgresql 7.3.1 
running on the most garbage windows ever created (WinME).
Everything you need is to install necessary packages and then:
ipc-daemon &
initdb -D /var/pgsql
postgresql -D /var/pgsql

Regards,
Tomasz Myrta


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Format Function

[Yudie]

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" <[EMAIL PROTECTED]>
To: "Yudie" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
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




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] convert from an integer to a date

[Frank Bax]

At 04:33 PM 2/14/03, [EMAIL PROTECTED] wrote:

When I run select to_date('20030212','MMDD') the output is 2/12/03
if I run select to_date( to_char(20030212,),'MMDD'); the 
output is 6/23/05

How can I convert from integer into date format correctly?


Same as the first one, except leave out the quotes.
select to_date(20030212,'MMDD');


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster