Re: Simple method to format a string

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