Thread: apply text mask

apply text mask

From
Andy Colson
Date:
I need to apply arbitrary masking to a string (think displaying a phone
number).

I searched around but didnt find anything and wondered if there was such
a thing.

I had some perl code that already did it, so it was easy to make it into
a stored proc.  I'll paste it below.

use it like:
select applyMask('(000) 000-0000', '1235551313');

returns:
(123) 555-1313

So this email is two fold.

1) is there something already built in that does this?
2) if not, here's one in plperl you can use if you like.


-- zero is the replace character, everything else is copied literally
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;


-Andy

Re: apply text mask

From
Andy Colson
Date:
On 12/10/2009 4:52 PM, Andy Colson wrote:
> I need to apply arbitrary masking to a string (think displaying a phone
> number).
>
> I searched around but didnt find anything and wondered if there was such
> a thing.
<SNIP>

Sorry, I should have also mentioned, I'm not using a numeric field.  Its
a string field.  And its not a phone number, its a parcel number (which
can contain letters) (I thought phone would be a more common example).
Every county has a different parcel number length and mask.

-Andy