SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to format the phone number in the result?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

argon007
Starting Member

38 Posts

Posted - 05/30/2008 :  00:36:39  Show Profile  Reply with Quote
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 - 05/30/2008 :  01:07:26  Show Profile  Reply with Quote
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 - 05/30/2008 :  01:37:41  Show Profile  Reply with Quote
thank you so much.
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 05/30/2008 :  10:46:07  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/30/2008 :  13:04:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000