CODEWITHSUNDEEP
sqlpostgresql
The Jug
The Jug

Reputation: 1165

SQL - Combining multiple like queries

Hey my first question on SO! Anywho...

Still relatively a newb at SQL so I think I might be missing something here. My question is I currently have a table full of phone numbers. I want to have a query where I search for phone numbers that are similar to a list I have. So for example, I want to find phone numbers that begin with '555123', '555321', and '555987'. I know normally if you have a list of numbers you could just do a query such as

SELECT * 
  FROM phonenumbers 
 WHERE number in ('5551234567', '5559876543', .... );

Is there a way to do this with like? Such as

SELECT * 
  FROM phonenumbers 
 WHERE number in like ('555123%', '555321%', '555987%'); //I know this doesn't actually work

Instead of have to do this individually

SELECT * 
  FROM phonenumbers 
 WHERE number like '555123%' 
    or number like '555321%' 
    or number like '555987%'; //Which does work but takes a long time

Or is there an easier to do this that I'm just missing? I'm using postgres, I don't know if there's any commands it has that would help out with that. Thanks!

Upvotes: 45

Views: 48954

Answers (6)

Quassnoi
Quassnoi

Reputation: 425371

Assuming all your numbers do not contain letters, and your numbers are always "prefixes" (ex: LIKE '123%'):

SELECT  number
FROM    (
        VALUES
        ('555123'),
        ('555321'),
        ('555000')
        ) prefixes (prefix)
JOIN    phonenumbers
ON      number >= prefix
        AND number < prefix || 'a'

This will use an index on phonenumbers, if any, so could be faster.

Upvotes: 7

RickyA
RickyA

Reputation: 16029

Late to the party, but for posterity... You can also use a ANY(array expression)

SELECT * 
FROM phonenumbers 
WHERE number LIKE ANY(ARRAY['555123%', '555321%', '555987%'])

Upvotes: 45

Victor Farazdagi
Victor Farazdagi

Reputation: 3140

You can also rely on POSIX regular expressions, see section 9.7.3 of the official documentation.

For example:

SELECT * FROM foobar WHERE name ~ '12345|34567';

It is important to note that your name field is of a string type.

Upvotes: 4

Pentium10
Pentium10

Reputation: 207912

You can use SIMILAR TO and separate the tags with | pipe '555123%|555321%|555987%'

eg:

SELECT * 
FROM phonenumbers 
WHERE number SIMILAR TO '555123%|555321%|555987%'

Upvotes: 78

toasteroven
toasteroven

Reputation: 2790

I don't think so, but you could join phonenumbers on a table criteria containing the values you want to match on, i.e.

JOIN criteria ON phonenumbers.number LIKE criteria.phonenumbers

...probably not worth it for a small number of conditions, though

Upvotes: 3

flybywire
flybywire

Reputation: 273532

Maybe if your prefixes are all the same length then you can do where RIGHT(number) in ('123456', '234456', 'etc', 'etc')

Upvotes: 1

Related Questions