MSSQL 2000 select int column to display as phone number

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!
LVL 1
smyers051972Asked:
Who is Participating?
 
JestersGrindCommented:
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
 
smyers051972Author Commented:
Thank you!
Was able to get that incorperated nicely!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.