SQL - Combining multiple like queries

Question

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!

Welcome to SO! I've retagged your question to clarify it relates to postgres — AdaTheDev, Feb 11 '10 at 15:41
What version of Postgres? Sounds like a job for regexes: http://www.postgresql.org/docs/8.3/static/functions-matching.html — OMG Ponies, Feb 11 '10 at 15:47
`Which does work but takes a long time` - unless you change the nature of the query, a different syntactic representation of the same thing will take as long. Is the column indexed? — MattH, Feb 11 '10 at 15:52
Actually the column is indexed. I hadn't noticed that before. Oh, and I am using postgres 8.0. I have a large amount of numbers I am searching. — The Jug, Feb 11 '10 at 15:59
How long does your query take? How many records you have in your table? — Pentium10, Feb 11 '10 at 16:00
Your query will result in an index search and as many comparisons for each item as you have conditions in your `where` clause. If you need these queries to be faster, you need to introduce an index that is more suited to your type of query. — MattH, Feb 11 '10 at 16:14
Hmm I just realized that there is another cron running on the system that is currently inserting records into this table, which is no doubt impacting my query performance. I'll have to try some of these searches after that it is done (it's taking 5+ minutes to return results for my queries). Darn crons running at inconvenient times ;) — The Jug, Feb 11 '10 at 16:18
As for how many records, it's not actually a table of specifically numbers but calls with the numbers and other such data. So it's like in the millions. (sorry for the double post) — The Jug, Feb 11 '10 at 16:21
transfer the data in a holding table, for temporary use, and try queries on it (that will be the true benchmark as won't be affected by reads and locks of any running connection) — Pentium10, Feb 11 '10 at 16:25

score 72 · Accepted Answer · answered Feb 11 '10 at 15:52

72

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

answered Feb 11 '10 at 15:52

Pentium10

204,586
122
423
502

RickyA · Answer 2 · 2018-07-04T07:27:26.163

40

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

edited Jul 04 '18 at 07:27

answered Nov 19 '14 at 10:48

RickyA

15,465
5
71
95

FWIW there's also a "LIKE ALL(...)" option https://stackoverflow.com/a/5742019/32453 – rogerdpack Sep 26 '18 at 17:32

score 7 · Answer 3 · edited Sep 26 '18 at 18:28

7

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.

edited Sep 26 '18 at 18:28

rogerdpack

62,887
36
269
388

answered Feb 11 '10 at 16:03

Quassnoi

413,100
91
616
614

Do you have a blog or intentions to write a book about these? I would be among the buyers. – Pentium10 Feb 11 '10 at 16:19
2

@Pentium10: http://explainextended.com The book is in progress, but this is a secret yet, please don't tell anybody. – Quassnoi Feb 11 '10 at 16:21

score 4 · Answer 4 · answered Oct 06 '13 at 17:02

4

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.

answered Oct 06 '13 at 17:02

Victor Farazdagi

3,080
2
21
30

Not sure why but this solution was the only one that worked for me. – TheLastGIS Apr 21 '20 at 01:01

score 3 · Answer 5 · answered Feb 11 '10 at 15:45

3

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

answered Feb 11 '10 at 15:45

toasteroven

2,700
3
26
35

Actually I do have a lot more to search but thought I should keep it simple with just a few numbers. – The Jug Feb 11 '10 at 15:47
or create temp table. Probably won't be any speedup from other options but an option whoa. – rogerdpack Sep 26 '18 at 18:17

score 1 · Answer 6 · answered Feb 11 '10 at 15:42

1

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

answered Feb 11 '10 at 15:42

flybywire

261,858
191
397
503

SQL - Combining multiple like queries

6 Answers6

Linked