How do I select the line that appears in the text list?
I have a SqlServer clients table
customer (first_name, last_name, home_phone, cell_phone)
and a list of text files for 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 customer records where the house or cell phone number appears in the text list?
The text list is about 1000 numbers, so I would like to just insert them once. How can I create a temporary table of numbers to execute a query?
SELECT first_name, last_name
FROM customer
WHERE home_phone IN (
SELECT * FROM temporary_table
)
But this does not fit the customer's phone number format and only checks the home phone number, not the cell phone number.
a source to share
To load values into a table use:
CREATE TABLE numbers (number VARCHAR(20))
BULK INSERT numbers
FROM 'c:\path_to\numbers.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n'
)
Note that it 'c:\path_to\numbers.csv'
must be accessible this way by the server, not the client.
This involves setting permissions for the account that your server is running on.
For request use:
SELECT *
FROM customer
WHERE home_phone IN
(
SELECT '+1' + number
FROM numbers
)
OR cell_phone IN
(
SELECT '+1' + number
FROM numbers
)
Don't use SUBSTRING
for home_phone
and cell_phone
: this will prevent indexes from being used to access those fields and make your query less efficient.
a source to share
You can use OR
to check multiple conditions and to remove the presenter : SUBSTRING
+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 function is SUBSTRING
not standard ANSI SQL and therefore will differ slightly between databases, eg. it can be called SUBSTR
.
a source to share
I would use a substring of the phone number for comparison. For instance:
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'
))
a source to share
For a lark, you can try putting the numbers in an xml object that looks like an array (i.e. you can pass this as parameters to a stored procedure) and then wipe it on a relation and join it to the customers 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
a source to share
SELECT * FROM customer WHERE home_phone = REPLACE("+1", "", "+19876543210");
Check out other great MySQL string functions here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
a source to share