asked on

t-sql phone number formatting

Hi experts,

I'm using sql server 2008.

I have a table with a column called Phones1 that holds phone numbers.
Some phone numbers are 7 digits and some are 10 digits.

I have a select query that gets all the columns from my table

if a phone number is 7 digits I want to format it like this: 123-4567
if a phone number is 10 digits I want to format it like this: (204)123-4567

What's the best way to format this column like this in my query?

Raghavendra Hullur

Hi,

Try this:

Case when len(Phone1) = 7 then concat(left(Phone1, 3),'-',right(Phone1,4))
when len(Phone1)= 10 then concat('(',left(Phone1, 3),')',substring(cast(Phone1 as varchar),4,3),'-',right(Phone1,4)) end as your_column_name

Regards,
Raghavendra

Scott Pletcher

SELECT CASE WHEN LEN(phone_number) > 7 THEN '(' + LEFT(phone_number, LEN(phone_number) - 7) + ')' ELSE '' END + STUFF(RIGHT(phone_number, 7), 4, 0, '-')

maqskywalker

ASKER

This is my table cols

This is my table

Code to create table:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestEmployees1](
	[EmployeeID] [int] NULL,
	[LastName] [varchar](50) NULL,
	[FirstName] [varchar](50) NULL,
	[EmployeePhone] [varchar](20) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[TestEmployees1] ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (1, N'Duck', N'Donald', N'1234567')
INSERT [dbo].[TestEmployees1] ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (2, N'Duck', N'Daffy', N'2088675902')
INSERT [dbo].[TestEmployees1] ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (3, N'Bunny', N'Bugs', N'3158675309')
INSERT [dbo].[TestEmployees1] ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (4, N'Leghorn', N'Foghorn', N'8526475')
INSERT [dbo].[TestEmployees1] ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (5, N'Martian', N'Marvin', N'4159864255')
INSERT [dbo].[TestEmployees1] ([EmployeeID], [LastName], [FirstName], [EmployeePhone]) VALUES (6, N'Lepew', N'Pepe', N'4537896')

Open in new window

The blue column in this sketch is my desired result: