postgres in condition with like

Question

SELECT first_name FROM "profiles" where first_name in ('one','Ankita')

I would like to run above query in such manner.

SELECT first_name FROM "profiles" where first_name in ('on%','Ank%')

Is it possible? if yes then it should return first_name start with 'on' and 'An'.

Can you cocatenate them with '|' characters between them? That would seem to work without requiring an 'in' clause, and would probably be more efficient. — Dylan Brams
– Dylan Brams, Commented Dec 31, 2013 at 18:48
@DylanB: But | doesn't mean anything special with LIKE, you'd have to switch to SIMILAR TO or a regex and that might over complicate things (or not). — mu is too short
– mu is too short, Commented Dec 31, 2013 at 18:56

mu is too short · Accepted Answer · 2014-03-17 18:14:25Z

2

You can use ANY with an array:

9.23.3. ANY/SOME (array)
expression operator ANY (array expression)
expression operator SOME (array expression)
The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the array has zero elements).

So you can say things like:

select first_name
from profiles
where first_name like any (array['on%', 'Ank%'])

You could also use a regex instead of LIKE:

first_name ~  '^(on|Ank)'  -- Case sensitive
first_name ~* '^(on|Ank)' -- Case insensitive

edited Mar 17, 2014 at 18:14

answered Dec 31, 2013 at 18:53

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

3 Comments

Craig Ringer Over a year ago

In fact, PostgreSQL transforms IN (...) into = ANY (...) internally.

mu is too short Over a year ago

@CraigRinger: A transformation I've seen in my SQL schema dumps. This reminds me a bit of some "stupid ORMs!" ranting we've done lately :)

Craig Ringer Over a year ago

<operator> ANY (...) is SQL standard syntax too, so that particular transformation wouldn't be cause for complaint, = ANY (...) is just as good as IN (...). As for "stupid ORMs" - yep, but "stupid !@# SQL" too, the standard is to blame for some of the portability issues (failure to standardize obvious things like OFFSET / LIMIT for over a decade, imagining new and insane syntax for every new feature). As are implementors, of course.

Collectives™ on Stack Overflow

postgres in condition with like

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related