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