CODEWITHSUNDEEP
sql
Stavros B
Stavros B

Reputation: 177

Use SQL Select query as wildcard

Currently, I have the following query:

UPDATE table1 SET P='1'
WHERE Recipient IN
(SELECT phone FROM phones WHERE number IN
(SELECT Number FROM users WHERE Username='test'))

So, i update a table column with a value where recipient column is found in a list with another criteria. Now, recipient is a phone number and from the incoming data it may be 10 digit or bigger if it has country code in front of it. How, could i change the above query so i can see if recipient is in a list of results but LIKE those results, in order to use wildcards?

Thank you in advance

Upvotes: 0

Views: 51

Answers (2)

Emilio Lucas Ceroleni
Emilio Lucas Ceroleni

Reputation: 1598

Using LIKE clause you can try to match phone numbers with the help of the % operator. So +1 (813) 890-4530 should match %(813) 890-4530. As you haven't posted examples of data in both recipient and phone tables I will assume the only difference that may exist is the country code. As opposed to IN clause I also suggest INNER JOIN when possible.

UPDATE table1 SET P='1'
FROM phones as p inner join users as u on p.number = u.Number
cross join table1 WHERE  Username='test' and (Recipient LIKE '%' + phone OR phone LIKE '%' + Recipient)

Upvotes: 1

Francisco Goldenstein
Francisco Goldenstein

Reputation: 13767

There are many options:

1) Apply a function to clean up phone numbers so they all look the same. For ex:

UPDATE table1 SET P='1' WHERE dbo.CleanPhone(Recipient) IN (SELECT phone FROM phones WHERE number IN (SELECT Number FROM users WHERE Username='test'))

2) Do an UPDATE using INNER JOIN instead of IN and use LIKE in the JOIN conditions. Something like this:

UPDATE t
FROM Table1 t
INNER JOIN Phones p ON Recipient LIKE ...

Upvotes: 0

Related Questions