Обсуждение: Simple method to format a string

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

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

> 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

Great.

After combined with several more replace(s), regexp_replace will provide 
me the expecting result.

Thanks!
Emi

-- 
select
regexp_replace(   replace(       replace(col-val, ' ', ''), '-', ''),        .... replace...    '(...)', E'\\1 ', 'g')
from tn;