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?

Answer 1

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 + '%')