On entry, I agree the client side will handle it (and if properly
coded, you'll never be able to enter erroneous stuff as you suggest -
mask logic ignores when users try to 'reenter' things like the
punctuation - and only recognizes and validates true digit entry)

But lets focus on selecting data OUT of the database... and lets assume
that the data is correctly stored in the DBase with NO punctuation.

On a select (From a Java app ? or maybe just from using interactive
SQL ? or ... ?) I'm asking whether or not using an UDF to do the
formatting makes sense.

So... lets say I have the following on the database as column tele1:

8189097749

With an UDF, a nice way might be:

select nicephonenum from <table>

Rather than the ugly example I gave before:


select '(' || substr(tele1,1,3) || ') ' || substr(tele1,4,3) || '-' ||
sright(tele1,4)


So... does such an UDF exist ? How do others handle extracting nicely
formatted phone numbers from tables ??

Thanks

Rich


Alan wrote:

this is really something for your client layer to handle.
Have you thought what will happen with this function if your users are
permitted (i.e. no mask provided for them) to enter
123-4567890
or
(123)4567890
or
123 4567890
??
or what happens if they enter (818) 522-3329?
Plus it's no fun for a user to have to enter
1234567890
and work out if they've entered a mistaken digit.
The result will be mess.
Alan

> > Thanks Daniel and Lucas for replies.
> >
> > Should have mentioned - I'm located in that big country with gas
> > guzzling cars (Los Angeles, to be exact).
> >
> > My needs are much simpler here than the complex number issues you have -
> > and I'm not allowing for non-US numbers.
> >
> > So all I'm wanting to do is turn:
> >
> > 8185223329
> >
> > into:
> >
> > (818) 522-3329
> >
> > (which was why I said simple picture masks for languages like Delphi
> > take care of lots of headaches for user entry).
> >
> > I'm really wondering if storing as a compact string of all digits OUT
> > WEIGHS the overhead of using a function to present the format more
> > politely.
> > Looking quickly at Apendix I in Helen's book, something like this could
> > get me there :
> >
> > select '(' || substr(tele1,1,3) || ') ' || substr(tele1,4,3) || '-' ||
> > sright(tele1,4)
> >
> > (but, thats ugly, eh ??). I just thought a UDF to handle the whole
> > thing could help - but just wanted to see if there was one already (not
> > sure i'm up to writing them yet)
> >
> > Thanks again
> >
> > Rich Pinder
> > USC School of Medicine
>
>