Format Telepone Number

I am using a stored procedure to query data in a SQL backend. I have the telephone number stored as 9999999999. I am wondering how I can get that number formatted and displayed in a list box (Access 2000). I want standard formatting, (999) 999-9999. How can I do this? Thanks.create function dbo.fn_FormatUSPhone (
@.PhoneString varchar(10) ) returns char(14)
as begin
declare @.RetVal char(14), @.Area char(3), @.Prefix char(3), @.Nbr char(4)
if isnumeric(@.PhoneString) = 0
set @.PhoneString = replicate('0', 10)
if datalength(@.PhoneString) < 10
set @.PhoneString = replicate('0', 10-datalength(@.PhoneString))+@.PhoneString
set @.Area = cast(@.PhoneString as char(3))
set @.Prefix = substring(@.PhoneString, 4, 3)
set @.Nbr = reverse(cast(reverse(@.PhoneString) as char(4)))
set @.RetVal = '(' + @.Area + ') ' + @.Prefix + '-' + @.Nbr
return @.RetVal
end
go
select field1, field2, dbo.fn_FormatUSPhone(phone_number_field) from your_table|||If you are sure your table inserts are 10 digits all the time

select '('+left(phone,3)+') '+substring(phone,4,3)+'-'+right(phone,4) from PhoneBook

This will return exactly the same result as the last reply|||Thank you very much. That's got it.|||Okay, I thought that had it. I would like to store my telephone numbers as datatype bigint, but now the code no longer works. Apparently you cannot use the substring function on a bigint datatype. Is it possible to use a bigint datatype and still have the format I am looking for. Please help one more time.|||Do NOT store your phone number as bigint. Are you going to add phone numbers? Are you going to divide phone numbers? Are you going to average phone numbers?

Phone numbers are character data that happen to be numerals.

blindman|||yeah, bigint is kinda odd|||I assume then that Social Security Numbers should also be stored as varchar?|||you betchya, just set up check constraints with [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] and you'll be all set.|||Actually, there are more consideration to choose the one or the other datatype.

Most importantly, your data type is part of your data model, especially your integrity rules. If in your model your telephone number are ten digits, you may use bigint, char(10) or three smallints; none of them are fitting your requirement completely, but you will have to add some check constraints like the SQL_DBA showed for char(10) or < 10000000000 and >= 1000000000 for bigint.

When you ensured that your data is correct, there is also the aspect of usage. Than numeric operators are not applicable, isn't really an argument not to choose a numeric data type. However, the possibility of proper formatting may be an reason to choose the char(10) option. Another aspect is storage, in some case it may be important that bigint needs 2 bytes less than char(10). An index will also be smaller, and maybe the engine will also faster compare two numbers than two strings.

However, I was wondering whether you model is fine. Is the part (999) your net number? In this case, you model would be correcter if you could ensure that you only have valid net numbers.|||Use bigint, and you will eventually wish you hadn't.

You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.

blindman|||Originally posted by blindman
Use bigint, and you will eventually wish you hadn't.

You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.

blindman

As I tried to explain, this is just one aspect. Optimized for presentation, you would even split the telefone number into three alphanumeric portions. Sematically, however, I would propose to model a separate net number, don't you agree?|||I can see a reason for splitting off the net number (I think you are referring to "area code" in U.S.) into a separate field in some applications. To handle all possible international phone formats, I'm not sure if it could be broken apart in a consistent and reliable manner.

blindman|||The advantage of using varchar outweighs the space savings in using bigint? That was my only question. I know all telephone numbers will be stored as 9999999999, so I thought I should use bigint and enjoy the space savings. Regardless, noone has posted indicating I can format the bigint number as (999) 999-9999, so I am stuck with varchar either way. Is that correct?|||You can format it that way after you cast your bigint value as char.

'(' + Left(cast(YourValue as varchar(10)), 3) + ') ' + Mid(cast(YourValue as varchar(10), 4, 3) + '-' + right(cast(YourValue as varchar(10), 4)

It is not a matter of can, but a matter of should. Any performance boost or space-savings you get from using bigint will be offset by added processing and administration time.

blindman|||I just saw that you are working with an Access 2000 front-end. Why don't you format your number at the front-end, using the format function:

format(<Your BigInt Number>, "(###)####-###")|||Apparently because I am using a stored procedure to call the data, and then displaying it in a list box, I have to format it at the stored procedure level. I assume this to be correct.|||No, I don't think so. Let your stored proc return the numeric value with a recordset, and make sure that you fill your listbox within a procedure, looping your recordset and formatting your number during adding to the list.|||Before I waste any more of your time, I think I will keep the phone number stored as varchar. The posts I have seen seem opposed to using bigint as the datatype. The space savings would be nice, but I've already got the code working for varchar and knowing that a lot of people are opposed to bigint makes me think I should stick with varchar. Thanks for your help and everyone else's in the group. You all have been extremely helpful.