?
Solved

MSSQL 2000 select int column to display as phone number

Posted on 2009-04-09
2
Medium Priority
?
336 Views
Last Modified: 2013-11-15
Right now I have a INT column (MSSQL 2000) called "homephone1" and when you put a number in it looks like this "5555555555".   How can I make it look like this "555-555-5555" or possibly "(555) 555-5555" without changing the actual data itself?

I need to avoid using any functions to do this, need to perhaps do this with replace?

Thanks for your help!
0
Comment
Question by:smyers051972
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 24110406
You column has to be a BIGINT column because that number won't fit into INT, but all you have to do is some string manipulation like this.

Greg



DECLARE @num BIGINT
 
SET @num = 5555555555
 
SELECT '(' + SUBSTRING(CONVERT(VARCHAR, @num), 1, 3) + ') ' + SUBSTRING(CONVERT(VARCHAR, @num), 4, 3) + '-' + SUBSTRING(CONVERT(VARCHAR, @num), 7, 4)

Open in new window

0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31568666
Thank you!
Was able to get that incorperated nicely!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we’ll look at how to deploy ProxySQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question