Re: [SQL] Simple method to format a string

[Wes James]

On Wed, Jun 20, 2012 at 8:42 AM, Emi Lu <em...@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