Remove symbols from a phone number field

[Guest]

I have a field that stores phone numbers as (512) 123-4567. I'd like to
remove the symbols so I just have 5121234567. How would I do this in an
update query?

Thanks in advance for your help!

 

[Joseph Meehan]

I have a field that stores phone numbers as (512) 123-4567. I'd like to
remove the symbols so I just have 5121234567. How would I do this in
an update query?

Thanks in advance for your help!

Has it really stored the numbers that way or are you viewing it
formatted that way?

Assuming it is stored that way, are they all stored with that exact
format?

 

[Guest]

Yes, they are all stored with the exact format. I'm not sure if they are
really stored that way, but when I attempt to extract the last 5 digits, it
returns the dash vs just the digits. (e.g. -4567 instead of 34567). I had
gone back in the properties of the field and ask that it not store the
symbols, but it didn't change the problem with extracting.

Thanks!

 

[Joseph Meehan]

Yes, they are all stored with the exact format. I'm not sure if they
are really stored that way, but when I attempt to extract the last 5
digits, it returns the dash vs just the digits. (e.g. -4567 instead
of 34567). I had gone back in the properties of the field and ask
that it not store the symbols, but it didn't change the problem with
extracting.

I think you just proved that the data is stored as a text and you are
seeing it displayed as a phone number. Take a look at all the field
properties and remove the input mask. That should do it. However if it is
phone numbers I would tend to leave it as it is.

 

[Z]

in my opinion i would instead use the number field instead of a text
field and then you could use an input mask to limit the amount of
numbers they have to type or are allowed to type. I have the reverse
problem, i want to have the - but instead it wont allow me to put the
dash because its a number field and not a text field. so when i type in
numbers like 808-100-0988 then it formats it at 8081000988

Z

 

[Douglas J. Steele]

I would recommend very strongly against using a numeric field. You should
never store data as a number strictly because it's numeric: you only store
it as a number if you need to be able to do calculations with its value.

Long Integers can only store a maximum value of 2,147,483,647, which renders
it incapable of storing a phone number for an area code of 215 or higher, as
well as not being able to store area code 214 phone numbers with an exchange
of 749 or higher.