Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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:
    Code:
    CREATE   FUNCTION ParsePhoneNo (@phone NVARCHAR(20), @parse_type INT)
    RETURNS NVARCHAR(20)
    AS
    BEGIN
    
    
    /*Function parses phone numbers to and from plain format.       */
    /*Eg: 1234567890 -> (123)456-7890 & (123)456-7890 -> 1234567890 */
    /*Usage:							*/
    /*   	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 
    BEGIN
    	SET @phone = REPLACE(@phone, '(', '')
    	SET @phone = REPLACE(@phone, ')', '')
    	SET @phone = REPLACE(@phone, ' ', '')
    	SET @phone = REPLACE(@phone, '-', '')
    END
    ELSE
    BEGIN
    	SET @phone = '(' + LEFT(@phone, 3) + ')' + SUBSTRING(@phone, 4, 3) + '-' + RIGHT(@phone, 4)
    END
    
    SET @X = @phone
    
    RETURN @X
    
    
    END
    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.

    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:

    UPDATE yourTable
    SET yourPhone = dbo.ParsePhoneNo(yourPhone, 0)
    Last edited by Teddy; 09-29-04 at 11:27.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    660
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    660
    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?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •