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
– AdaTheDev, Commented Feb 11, 2010 at 15:41
What version of Postgres? Sounds like a job for regexes: postgresql.org/docs/8.3/static/functions-matching.html — OMG Ponies
– OMG Ponies, Commented Feb 11, 2010 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
– MattH, Commented Feb 11, 2010 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
– The Jug, Commented Feb 11, 2010 at 15:59
How long does your query take? How many records you have in your table? — Pentium10
– Pentium10, Commented Feb 11, 2010 at 16:00

Pentium10 · Accepted Answer · 2010-02-11 15:52:53Z

81

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, 2010 at 15:52

Pentium10

209k124 gold badges435 silver badges513 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RickyA · Accepted Answer · 2018-07-04 07:27:26Z

46

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 4, 2018 at 7:27

answered Nov 19, 2014 at 10:48

RickyA

16.1k6 gold badges77 silver badges97 bronze badges

1 Comment

rogerdpack Over a year ago

FWIW there's also a "LIKE ALL(...)" option stackoverflow.com/a/5742019/32453

rogerdpack · Accepted Answer · 2018-09-26 18:28:46Z

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, 2018 at 18:28

rogerdpack

67.7k40 gold badges290 silver badges409 bronze badges

answered Feb 11, 2010 at 16:03

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

2 Comments

Pentium10 Over a year ago

Do you have a blog or intentions to write a book about these? I would be among the buyers.

Quassnoi Over a year ago

@Pentium10: explainextended.com The book is in progress, but this is a secret yet, please don't tell anybody.

Victor Farazdagi · Accepted Answer · 2013-10-06 17:02:18Z

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 6, 2013 at 17:02

Victor Farazdagi

3,1502 gold badges25 silver badges30 bronze badges

1 Comment

user1333920 Over a year ago

Not sure why but this solution was the only one that worked for me.

toasteroven · Accepted Answer · 2010-02-11 15:45:16Z

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, 2010 at 15:45

toasteroven

2,8004 gold badges26 silver badges35 bronze badges

2 Comments

The Jug Over a year ago

Actually I do have a lot more to search but thought I should keep it simple with just a few numbers.

rogerdpack Over a year ago

or create temp table. Probably won't be any speedup from other options but an option whoa.

flybywire · Accepted Answer · 2010-02-11 15:42:48Z

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, 2010 at 15:42

flybywire

276k201 gold badges407 silver badges509 bronze badges

Collectives™ on Stack Overflow

SQL - Combining multiple like queries

6 Answers 6

Comments

1 Comment

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related