Simple method to format a string?

On Jun 20, 2012, at 7:43 AM, Emi Lu 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'm unaware of such a function (it seems like a generic format() function's arguments would be so complex as to be almost unusable) but you can make use of locales to do some formatting for you. See http://www.postgresql.org/docs/9.1/static/locale.html.

This pretty much calls for a user defined function. Plpgsql should
work, but if you're more comfy in perl or tcl there's pl/tcl and
pl/perl you can try as well.

On Wed, Jun 20, 2012 at 8:43 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
To understand recursion, one must first understand recursion.

On 6/20/2012 12:59 PM, Scott Marlowe wrote:

This pretty much calls for a user defined function. Plpgsql should
work, but if you're more comfy in perl or tcl there's pl/tcl and
pl/perl you can try as well.

On Wed, Jun 20, 2012 at 8:43 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is the perl func I use: sorry about the formatting

-- mask char is 0 (zero). anything else gets copied across
create or replace function applyMask(text, text) returns text as $$
my($mask, $src) = @_;

my $srcAt = 0;
my $srcLen = length($src);
my $result = '';

for my $i (0..length($mask)-1)
{
my $mchar = substr($mask, $i, 1);
if ($mchar eq '0')
{
if ($srcAt >= $srcLen)
{
$result .= ' ';
} else {
$result .= substr($src, $srcAt, 1);
$srcAt++;
}
} else {
$result .= $mchar;
}
}
return $result;
$$ language plperl;

For example:

select applyMask('(000) 000-0000', '1235551313');

Emi Lu 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 -> *** *** ***

Depending on how you want 'extras' handled, you could work from
something like this:

select trim(regexp_replace('123456', '...', '\& ', 'g'));

If you don't care about trailing space remove the 'trim()'.

The replacement string may need to be escaped differently depending
on your PG version and setting for standard_conforming_strings. For
example: E'\\& '

HTH

Bosco.

select trim(regexp_replace('123456', '...', '\& ', 'g'));

If you don't care about trailing space remove the 'trim()'.

The replacement string may need to be escaped differently depending
on your PG version and setting for standard_conforming_strings. For
example: E'\\& '

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;