var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: phone number formate
I have a database front end using Access, backend using SQL server.
Some of the phone number in the databse has format like " (444) 444-4444".
I want to change all the data to be like "4444444444". How can i do that Aas there are lot of data there, Thanks
Use an update statement to parse out all the values.
I have to do this quite a bit so I wrote a server side UDF to do it for me, you can use it if you'd like:
It's missing some error checking on converting from a straight number into a parsed phone number. If you try to pass it a pre-parsed number it'll look pretty funny.
CREATE FUNCTION ParsePhoneNo (@phone NVARCHAR(20), @parse_type INT)
/*Function parses phone numbers to and from plain format. */
/*Eg: 1234567890 -> (123)456-7890 & (123)456-7890 -> 1234567890 */
/* dbo.ParsePhoneNo(phone, type[1, 0]) */
/*Parse Types: */
/* 0 = (xxx)xxx-xxxx -> xxxxxxxxxx */
/* 1 = xxxxxxxxxx -> (xxx)xxx-xxxx */
DECLARE @X AS NVARCHAR(20)
If @parse_type = 0
SET @phone = REPLACE(@phone, '(', '')
SET @phone = REPLACE(@phone, ')', '')
SET @phone = REPLACE(@phone, ' ', '')
SET @phone = REPLACE(@phone, '-', '')
SET @phone = '(' + LEFT(@phone, 3) + ')' + SUBSTRING(@phone, 4, 3) + '-' + RIGHT(@phone, 4)
SET @X = @phone
Going from parse to straight is fine though. Feel free to tweek it, I'd like if you reposted here if you choose to do so.
Anywho, you can of course use this in any query you'd like:
SET yourPhone = dbo.ParsePhoneNo(yourPhone, 0)
Last edited by Teddy; 09-29-04 at
Seems like you first need to see how many variations of that data you have. For instance do you have 444.444.4444 or 444-444-4444. Simple query would do that.
Thanks you very much! the phone number only have two format, one is 4444444444 and (444) 444-4444. I am not familar with UDF. I will learn that. Do you have any simple query to change the data? Thanks
Teddy, i have a question, where to write the udf, using query analyzer? where to save it. When i call the udf, i need to go the query analyer, then call this function, is that right?
You should really read up on udf's (user defined functions) before implementing that. Yes, you create the UDF in QA. Just cut and paste, then execute. The function will be available from wherever you run the query, including an access adp.
Read up on what that's doing. UDF's are one of the things that makes MSSQL so useful, you're shorting yourself if you don't tke a couple hours today to figure out how to use them.