LIKE operator instead of IN operator when checking tables

Question

I am working on an SQL script to add the ID of a customer if the phone number is found inside a table.

Below is a snippet of my code:

CREATE TABLE #Customers ( AccountNumber nvarchar(20))
CREATE TABLE #Phones ( Number nvarchar(30))

INSERT INTO #Customers (AccountNumber)
    SELECT AccountNumber
    FROM CustomerTable
    WHERE 
        (CustomerTable.PhoneNumber IN (SELECT DISTINCT Number FROM #Phones))
        OR
        (CustomerTable.PhoneNumber2 IN (SELECT DISTINCT Number FROM #Phones))
GO

However, using those statement I am only able to check if the exact record is met within the table. For example: If Phones.Number has a record '123456' and CustomerTables.PhoneNumber has a record '123456 7', this record record will not be obtained, even if it contains the number.

Are their any modifications I can do to my current snippet in order to apply similar logic to the LIKE operator?

have you tried, where LEFT(PhoneNumber,6) in (SELECT DISTINCT Left(Number,6) FROM #Phones)), Actually its the other way — , Jun 22 '18 at 15:08
@JonathanBorg Your example only shows CustomerTable, where are you getting Phones.Number? — Ryan Wilson, Jun 22 '18 at 15:11
Is that what you want though? It seems it would be better to sanitize the data and use a stricter matching clause like `=` (or your existing `IN`). What if `p.Number` has the value `1` or `12`, it would return unintended matches. Or perhaps always match on the first X characters from the left. — Igor, Jun 22 '18 at 15:12
Should "KL5-1212" match "555-1212"? Is "BR-549 ring 3" close enough to "27549"? "(101) 555-1212" and "101-555-1212"? "666-000-0042 ext 2" and "1-666-000-0042"? Is there a default country code or do you pull one based on the customer's address? It's not a trivial problem. As Igor suggests, having separate columns for the customer's concept of their number ("1-800-GOT-STAK") and a standardized value ("800-468-7825") is a better way to go. — HABO, Jun 22 '18 at 15:49
@Igor, unfortunately data sanitization is not an option for this specific problem. — Jonathan, Jun 25 '18 at 07:23
@HABO, I reviewed the data and the format is quite straight forward, a set of integer numbers, followed by a special character (space or minus) and another set of integer numbers (d+{s* d+}*) — Jonathan, Jun 25 '18 at 07:25

score 4 · Accepted Answer · answered Jun 22 '18 at 15:11

This can't be done directly with the in operator, but using a query inside the exists operator should do the trick:

INSERT INTO #Customers (AccountNumber)
SELECT AccountNumber
FROM   CustomerTable ct
WHERE  EXISTS (SELECT *
               FROM   #Phones p
               WHERE  ct.PhoneNumber LIKE '%' + p.Number + '%' OR
                      ct.PhoneNumber2 LIKE '%' + p.Number + '%')

score 1 · Answer 2 · answered Jun 22 '18 at 15:22

1

SELECT AccountNumber
FROM CustomerTable c 
join #Phones p
  on c.PhoneNumber like p.Number + '%' OR c.PhoneNumber2  like p.Number + '%'

answered Jun 22 '18 at 15:22

paparazzo

44,497
23
105
176

score 0 · Answer 3 · answered Jun 22 '18 at 15:15

I think this will also work, but i have not tested.

WITH Customers AS 
     (
        SELECT AccountNumber , Len(PhoneNumber) as PhoneSize
        FROM  
     )
INSERT INTO #Customers (AccountNumber)
SELECT AccountNumber
FROM CustomerTable a
Join Customers b on a.AccountNumber = b.AccountNumber
WHERE 
(CustomerTable.PhoneNumber IN (SELECT DISTINCT LEft(Number,b.PhoneSize) FROM #Phones))
OR
(CustomerTable.PhoneNumber2 IN (SELECT DISTINCT LEft(Number,b.PhoneSize) FROM #Phones))

LIKE operator instead of IN operator when checking tables

3 Answers3