MS SQL - A way to run a select query with specific requirements?

Posted on 2012-09-04
Last Modified: 2012-09-14
I have a database with phone numbers like so:


Pretty much, with the dashes.

Now, I have other databases with user information, including phones, but the data isn't in the same format, sometimes its with dashes, sometimes it without. aka 999-999-9999 or 9999999999.

I normally do a

select * into tempTable from personsTable where phone NOT IN (select phone from phoneTable)

to get the phone data. IS there a way to support both dashes and not dashes without me having to make a second db without the dashes and one with?

Question by:Valleriani
    LVL 32

    Expert Comment

    You can use the replace keyword to remove the dashes on the fly in your query
    Assuming the dashes are in the phone table, you can do this

    select * 
    into tempTable 
    from personsTable 
    where phone NOT IN (select REPLACE(phone, '-', '') 
                                       from phoneTable)

    Open in new window

    LVL 32

    Accepted Solution

    Or remove dashes from both tables using Exists, the only issue is that indexes are not used

    select * 
    into tempTable 
    from personsTable 
    where NOT exists (select null  
                                  from phoneTable
                                  where REPLACE(tempTable .(phone, '-', '') = REPLACE(PhoneTable .(phone, '-', ''))

    Open in new window

    LVL 25

    Expert Comment

    Select pertbl.*
    from personsTable pertbl
    where NOT exists (select 1
                                  from phoneTable pt
                                  where REPLACE(, '-', '') = REPLACE(, '-', ''))

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now