Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to format the phone number in the result?

Author  Topic 

argon007
Starting Member

38 Posts

Posted - 2008-05-30 : 00:36:39
like (###) ###-####.

I know that

SELECT phone
FROM author

however, the results will be like

408 496-7223.

but I do not know how to format the phone number.

can you help me to figure it out please?

thanks.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-30 : 01:07:26
quote:
Originally posted by argon007

like (###) ###-####.

I know that

SELECT phone
FROM author

however, the results will be like

408 496-7223.

but I do not know how to format the phone number.

can you help me to figure it out please?

thanks.


SELECT '('+SUBSTRING(Phone,1,3)+')'+SUBSTRING(Phone,4,LEN(Phone))
FROM Author
Go to Top of Page

argon007
Starting Member

38 Posts

Posted - 2008-05-30 : 01:37:41
thank you so much.
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-05-30 : 10:46:07
That works fine with 10 digit numbers, but fails if the number does not include the area code.

Try this:

create function [dbo].[FormatPhone](@PhoneNum varchar(50))
returns varchar(50)
as
-- FormatPhone
-- blindman, 3/2004
-- Formats a string of 7-10 numeric values as a phone number, with or without the area code.
begin
-- Test parameters
-- declare @PhoneNum varchar(50)
-- set @PhoneNum = '6142462473'
declare @ReverseNum varchar(50)
set @ReverseNum = Reverse(@PhoneNum)
return reverse(left(@ReverseNum, 4) + '-' + substring(@ReverseNum, 5, 3) + coalesce(' )' + nullif(substring(@ReverseNum, 8, 3), '') + '(', ''))
end


e4 d5 xd5 Nf6
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 13:04:23
If possible, its better to do this type data formating at your front end application which provides with lots of flexible functions for formatting.
Go to Top of Page
   

- Advertisement -