Обсуждение: 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 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
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;