pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Simple method to format a string
4+ messages / 3 participants
[nested] [flat]

* Simple method to format a string
@ 2012-06-20 14:42 Emi Lu <emilu@encs.concordia.ca>
  2012-06-20 18:08 ` Re: Simple method to format a string Wes James <comptekki@gmail.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Emi Lu @ 2012-06-20 14:42 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Simple method to format a string
  2012-06-20 14:42 Simple method to format a string Emi Lu <emilu@encs.concordia.ca>
@ 2012-06-20 18:08 ` Wes James <comptekki@gmail.com>
  2012-06-20 18:21   ` Re: Simple method to format a string ktm@rice.edu <ktm@rice.edu>
  0 siblings, 1 reply; 4+ messages in thread

From: Wes James @ 2012-06-20 18:08 UTC (permalink / raw)
  To: emilu@encs.concordia.ca; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Simple method to format a string
  2012-06-20 14:42 Simple method to format a string Emi Lu <emilu@encs.concordia.ca>
  2012-06-20 18:08 ` Re: Simple method to format a string Wes James <comptekki@gmail.com>
@ 2012-06-20 18:21   ` ktm@rice.edu <ktm@rice.edu>
  2012-06-20 19:45     ` Re: Simple method to format a string Emi Lu <emilu@encs.concordia.ca>
  0 siblings, 1 reply; 4+ messages in thread

From: ktm@rice.edu @ 2012-06-20 18:21 UTC (permalink / raw)
  To: Wes James <comptekki@gmail.com>; +Cc: emilu@encs.concordia.ca; pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Simple method to format a string
  2012-06-20 14:42 Simple method to format a string Emi Lu <emilu@encs.concordia.ca>
  2012-06-20 18:08 ` Re: Simple method to format a string Wes James <comptekki@gmail.com>
  2012-06-20 18:21   ` Re: Simple method to format a string ktm@rice.edu <ktm@rice.edu>
@ 2012-06-20 19:45     ` Emi Lu <emilu@encs.concordia.ca>
  0 siblings, 0 replies; 4+ messages in thread

From: Emi Lu @ 2012-06-20 19:45 UTC (permalink / raw)
  To: ktm@rice.edu <ktm@rice.edu>; +Cc: Wes James <comptekki@gmail.com>; pgsql-sql


> 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;





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2012-06-20 19:45 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-20 14:42 Simple method to format a string Emi Lu <emilu@encs.concordia.ca>
2012-06-20 18:08 ` Wes James <comptekki@gmail.com>
2012-06-20 18:21   ` ktm@rice.edu <ktm@rice.edu>
2012-06-20 19:45     ` Emi Lu <emilu@encs.concordia.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox