SQL 2000 TSQL question

Question

SQL 2000 TSQL question

sqldba-294117

Hall of Fame

Points: 3486
More actions
November 30, 2006 at 12:21 pm

#170509

I am creating table & one of column name is phonenumber. the number will be inserted as (1234567890) in column but i want that while inserting it should insert like this (123-456-7890). any help is appreciated & thanking in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You're mixing data requirements with presentation requirements.

What if you fill your database with formatted phone number strings like this, and then the requirement for how the dta is presented changes in the future ?

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

I agree with that! Store the phone number as a string of digits only... the other kludge that needs to be considered is are any international numbers ever going to be stored? Like PW said, don't store the formatting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Or store it as an integer like the others suggested and then add a computed column that formats it in the format you want

Alter

Table MyTable Add FormattedPhone As Stuff(Stuff(PhoneNumber, 4, 0, '-'), 8, 0, '-')

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Same phone number will be dialed (that means appeared for the user) in different ways from different locations.

Unless you mean to limit usage of your application to a single town you need to store international, long distance codes and numbers itself separately and build it according to location of the user and predefined rules specified for this location.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

You'd have to store it as a BIGINT... most phone numbers are too big for just INT. That would also guarantee numerics only.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Jackson SSCertifiable Points: 6414 More actions · Answer 6

It may be worth pointing out that if your number starts with a Zero (0), as in the UK, it needs to be stored as a varchar. I agree with the points above about not storing formatting though, and I'm especially impressed with Robert Davis's trick of

Stuff(Stuff(PhoneNumber, 4, 0, '-'), 8, 0, '-')

Dave

http://glossopian.co.uk/
"I don't know what I don't know."

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 7

Thank you very much for your all response/replies. this helped me to decide right datatype & formating. thanks again