Date: Wed, 20 Jun 2012 13:21:43 -0500
From: "ktm@rice.edu" <ktm@rice.edu>
To: Wes James <comptekki@gmail.com>
Cc: emilu@encs.concordia.ca, pgsql-sql@postgresql.org
Subject: Re: Simple method to format a string
Message-ID: <20120620182142.GF6547@aart.rice.edu>
References: <4FE1E14A.8060603@encs.concordia.ca>
	<CAFjCMHuPLR1aEHr8agWm1ihT+8XNRZcFeKkPY1oQ_GABPOMfGg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAFjCMHuPLR1aEHr8agWm1ihT+8XNRZcFeKkPY1oQ_GABPOMfGg@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-12-10)

On Wed, Jun 20, 2012 at 12:08:24PM -0600, Wes James wrote:
> On Wed, Jun 20, 2012 at 8:42 AM, Emi Lu <emilu@encs.concordia.ca> wrote:
> 
> > Good morning,
> >
> > Is there a simply method in psql to format a string?
> >
> > For example, adding a space to every three consecutive letters:
> >
> > abcdefgh -> *** *** ***
> >
> > Thanks a lot!
> > Emi
> >
> >
> I looked at "format" here:
> 
> http://www.postgresql.org/docs/9.1/static/functions-string.html
> 
> but didn't see a way.
> 
> This function might do what you need:
> 
> 
> CREATE FUNCTION spaced3 (text) RETURNS text AS $$
> DECLARE
>    -- Declare aliases for function arguments.
>   arg_string ALIAS FOR $1;
> 
>   -- Declare variables
>   row record;
>   res text;
> 
> BEGIN
>   res := '';
>   FOR row IN SELECT regexp_matches(arg_string, '.{1,3}', 'g') as chunk LOOP
>     res := res || ' ' || btrim(row.chunk::text, '{}');
>   END LOOP;
>   RETURN res;
> END;
> $$ LANGUAGE 'plpgsql';
> 
> 
> # SELECT spaced3('abcdefgh');
> 
>    spaced3
> -------------
>   abc def gh
> (1 row)
> 
> # SELECT spaced3('0123456789');
>     spaced3
> ----------------
>   012 345 678 9
> (1 row)
> 
> to remove the function run this:
> 
> # drop function spaced3(text);
> 
> -wes

Just a small optimization would be to use a backreference with regexp_replace
instead of regexp_matches:

select regexp_replace('foobarbaz', '(...)', E'\\1 ', 'g');
 regexp_replace 
----------------
 foo bar baz 
(1 row)

regards,
Ken