display leading zero in a numeric field [message #373939] |
Thu, 17 May 2001 11:11 |
Gail Kerwin
Messages: 1 Registered: May 2001
|
Junior Member |
|
|
Does anyone know if it's possible to preserve a leading zero if a number has one, and is loaded into a numeric field?
I have to use a set of tables that I can't change, which have phone number set as a numeric field. Leading zero's are being truncated when UK numbers like 02089023000 are loaded. Is there any way to keep the leading zero, if a phone number like this is loaded into a numeric field?
Many thanks in advance,
Gail
|
|
|
Re: display leading zero in a numeric field [message #373940 is a reply to message #373939] |
Thu, 17 May 2001 11:54 |
Andrew again...
Messages: 270 Registered: July 2000
|
Senior Member |
|
|
No, the presence of a leading 0 can't be stored in a numberic data type. You can work around it by adding a flag field to to table and then set the flag field from a trigger when you insert or update the value.
Also if you can determine busines rules to derive the presence or absence of the leading 0 based on the dial-code or length of the phone number that would work too, but you only find out that your algorithm is wrong when an exception pops up.
The current design has shortcomings which are probably best overcome by changing to varchar2. If you considered changing the data type though, consider that your sort order changes. To make numeric strings sort the same as numbers, left pad the strings to a fixed length with spaces.
|
|
|