IN operator in SQL (PostgreSQL)

Question

I'm trying to select with the IN operator in postgreSQL.

When I write:

select name from table where name = 'Eastway'

or

select name from table where name like '%Oxford%'

then I have multiple results but

select name from table where name in ('Eastway', '%Oxford%')

does not return any value.

What am I doing wrong? And apologies for newbie question.

I noticed the issue was at upper and lower case strings :)

Peter
– Peter

2015-11-27 00:05:36 +00:00
Commented Nov 27, 2015 at 0:05 — Peter
– Peter, Commented Nov 27, 2015 at 0:05

Juan Carlos Oropeza · Accepted Answer · 2015-11-26 23:26:11Z

2

LIKE is a regular expresion comparasion

= is exact match

Your IN is equivalent to name ='Eastway' OR name = '%Oxford%'

But if you say on your first query you get multiple result. On your IN version should also have some result as well. The rows where name ='Eastway'

answered Nov 26, 2015 at 23:26

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

1 Comment

zerkms Over a year ago

I would say LIKE is a pattern matching comparison operator, since it does not support regular expressions. For regexes there is another SIMILAR or ~ operators. postgresql.org/docs/9.3/static/functions-matching.html

Gordon Linoff · Accepted Answer · 2015-11-26 23:49:28Z

0

You can use or for this. Or a regular expression:

select name
from table
where name ~ '(^Eastway$)|Oxford';

answered Nov 26, 2015 at 23:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

IN operator in SQL (PostgreSQL)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related