dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Phone Number Formats

  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Question Phone Number Formats

    I have two different tables in an Access (2003) database file - each table has over 100,000 records. I need to compare and find duplicates within two different tables. The problem is that one table has the phone number formatted and appearing as "123 456-7890" and the other table has the phone number formatted as "123-456-7890". I can't create a "Find duplicates" query with the different phone number formats and I would like to format the numbers so there is a uniform display between the two tables.

    Can anyone help?

    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Posts
    161
    Use Replace() to get rid of all spaces and dashes so you are comparing like values.
    Last edited by Slope; 04-13-2008 at 01:02 AM.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  3. #3
    Join Date
    Apr 2008
    Posts
    3

    Question Thanks so much!! Another ?...

    Is it possible to add that expression to an SQL Union Query? I can't seem to get it to work, keeps returning a syntax error.

    Any help would be appreciated!


  4. #4
    Join Date
    Feb 2008
    Posts
    161
    Yes. Post your query so we can take a look.
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

  5. #5
    Join Date
    Apr 2008
    Posts
    3
    Here is my union query that I would like to add the Replace function to.
    Thanks again for all the help, I really appreciate it!


    SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT
    FROM [01-07 Dup Calls];
    UNION ALL
    SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT
    FROM [02-07 Dup Calls];
    UNION ALL SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT
    FROM [03-07 Dup Calls];

  6. #6
    Join Date
    Feb 2008
    Posts
    161
    Assuming ORIG_NUM is your phone number column, try:

    Code:
    SELECT 
        REPLACE(REPLACE(x.ORIG_NUM, "-", ""), " ", "") AS ORIG_NUM,
        x.DIALED_800, 
        x.AT_DESC, 
        x.CALL_DATE, 
        x.CNCT_TIME, 
        x.DURATION, 
        x.FR_PLACE_S, 
        x.FR_PLACE, 
        x.RP_DESC, 
        x.TERM_NUM, 
        x.USG_AMT
    FROM
    (
    SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT FROM [01-07 Dup Calls] UNION ALL 
    SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT FROM [02-07 Dup Calls] UNION ALL 
    SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT FROM [03-07 Dup Calls]
    ) AS x
    It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain

Similar Threads

  1. Replies: 18
    Last Post: 12-13-2006, 03:49 PM
  2. Prime number program to make go faster
    By divagoddess in forum C++
    Replies: 12
    Last Post: 05-07-2006, 10:55 PM
  3. Phone number search
    By Judy in forum VB Classic
    Replies: 4
    Last Post: 08-30-2000, 11:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center