How do I select a row that appears in a text list?

Question

I have a SqlServer customer table

customer (first_name, last_name, home_phone, cell_phone)

and a text file list of phone numbers like

9876543210,
4564561234,
1231231234,
1234567890,

The phone numbers in the customer table are stored in the format +1dddddddddd: where dddddddddd is the phone number.

How can I find all the customer records where the home or cell phone number appears in the text list?

The text list is about a 1000 numbers long so ideally I would only want to paste them once. How could I create a temporary table of the numbers to do the query on?

SELECT first_name, last_name
FROM customer
WHERE home_phone IN (
  SELECT * FROM temporary_table
)

But that doesn't match the customer phone number format and only checks against the home phone number and not the cell phone number as well.

Quassnoi · Accepted Answer · 2009-05-20T15:39:03.310

To load the values into the table, use:

CREATE TABLE numbers (number VARCHAR(20))

BULK INSERT numbers
FROM 'c:\path_to\numbers.csv' 
WITH 
( 
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\r\n' 
)

Note than 'c:\path_to\numbers.csv' should be accessible by this path by the server, not client.

This implies setting permissions for the account your server runs under.

To query, use:

SELECT  *
FROM    customer
WHERE   home_phone IN
        (
        SELECT  '+1' + number
        FROM    numbers
        )
        OR cell_phone IN
        (
        SELECT  '+1' + number
        FROM    numbers
        )

Do not use SUBSTRING on home_phone and cell_phone: this will prevent using indexes to access these fields and make your query less efficient.

very good point about the sargability of the substring query. — HLGEM, May 20 '09 at 15:26

David Webb · Answer 2 · 2009-05-20T14:03:39.147

0

You can use an OR to check multiple conditions and SUBSTRING to strip the leading +1:

SELECT first_name, last_name
FROM customer
WHERE SUBSTRING(home_phone,3,10) IN (
  '9876543210',
  '4564561234',
  '1231231234',
  '1234567890'
)
OR SUBSTRING(cell_phone,3,10) IN (
  '9876543210',
  '4564561234',
  '1231231234',
  '1234567890'
)

The SUBSTRING function is not standard ANSI SQL and so will vary slightly between databases, e.g. it may be called SUBSTR.

edited May 20 '09 at 14:03

answered May 20 '09 at 13:57

David Webb

190,537
57
313
299

Thanks, a substring or a replace will solve the different formats. Is there a way to place the numbers in a temporary table so I only have to paste the numbers in one place? – David Glenn May 20 '09 at 14:18
Have a look at this question: http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function – David Webb May 20 '09 at 15:09

score 0 · Answer 3 · answered May 20 '09 at 14:02

I would use a substring of the phone number to compare. For example:

select first_name
     , last_name
  from customer
 where ( substring(home_phone, 2, 10) in (
                       '9876543210',
                      '4564561234',
                      '1231231234',
                      '1234567890'
  )
  OR  substring (cell_phone, 2, 10) in (
                       '9876543210',
                      '4564561234',
                      '1231231234',
                      '1234567890'
  ))

score 0 · Answer 4 · answered May 20 '09 at 14:05

For a lark you could try putting the numbers in an xml object that looks like an array (ie you can pass this in as a parameters to a stored procedure) and then stread it to a relation and join it with your customer table:

declare @numbers xml
set @numbers = '<numbers>
<number value="9876543210"/>
<number value="4564561234"/>
<number value="1231231234"/>
<number value="1234567890"/>
</numbers>'

SELECT first_name, last_name 
FROM  customers
join @number.nodes('//number') as t(c)
 on c.value('@value','varchar(10)') = '+1'+customers.homephone

score 0 · Answer 5 · answered May 20 '09 at 14:14

0

SELECT * FROM customer WHERE home_phone = REPLACE("+1", "", "+19876543210");

Check out the other cool MySQL string features here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

answered May 20 '09 at 14:14

powtac

40,542
28
115
170

How do I select a row that appears in a text list?

5 Answers5