[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-09-04
Medium Priority
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
  • 2
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 38364863
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

Ephraim Wangoya earned 2000 total points
ID: 38364874
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

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

831 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