Postgres Pro
Downloads
Home   >   mailing lists

Re: Simple method to format a string - Mailing list pgsql-sql

From Wes James
Subject Re: Simple method to format a string
Date
Msg-id CAFjCMHuPLR1aEHr8agWm1ihT+8XNRZcFeKkPY1oQ_GABPOMfGg@mail.gmail.com
Whole thread Raw
In response to Simple method to format a string  (Emi Lu <emilu@encs.concordia.ca>)
Responses Re: Simple method to format a string  ("ktm@rice.edu" <ktm@rice.edu>)
List pgsql-sql
Tree view

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:


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

pgsql-sql by date:

Previous
From: Andreas
Date:
Subject: Is there a similarity-function that minds national charsets?
Next
From: "ktm@rice.edu"
Date:
Subject: Re: Simple method to format a string