Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sardinka
Posting Yak Master
142 Posts |
 Posted - 2005-05-09 : 14:59:10
|
| How to format phone numbers in sql? in current fields some phone number entered like this:1 800 7894564800123787812378781800blue etc...I am trying to get into uniform like this:8007834444Thanks |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-09 : 15:16:27
|
| You don't format the numbers in SQL. You need to format them when you present them in your presentation layer, or you need to enforce a standard format when you store them. i.e., you might break each component of the number out into a seperate column in your table, or add CHECK() constraints to your table to ensure the number is well-formed or have the presentation layer ensure the phone number is valid and well-formed before adding it to the DB.- Jeff |
 |
|
|
sardinka
Posting Yak Master
142 Posts |
Posted - 2005-05-09 : 15:27:43
|
| It is not a number it is a varchar(20). I wish I could do this before I entered the data into db. I did like this:Replace(Replace(Replace(Replace(Replace(replace(insurancephone,'-',''),'.',''),' ',''),'/',''),')',''),'(','')However I don't like this even it is do a job is it any better way? I can't change the structure of db. |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-05-09 : 15:51:32
|
| Unfortunately you have to do what you did and think of every way a user can screw up entering a phone number and try to account for it. This really should be something that your application should be responsible for before the data gets into the system. If you really want to make your point change your field to a char(10) and then go on vacation.Mike"oh, that monkey is going to pay" |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2005-05-09 : 15:52:07
|
| This need to be done on client side not on back-end SQL Server. Either using JavaScript or RegularExpressionValiation Control in ASP.net etc... |
|
|
|
|
|
|
|
|
format Phone number