Re: Simple method to format a string

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(at)encs(dot)concordia(dot)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