Is value on the list of values? (Oracle SQL)

Question

Below works for SQL Server and MySQL, but how to modify it to work as proper Oracle query:

SELECT city FROM station WHERE LEFT(city, 1) IN ('a', 'e', 'i', 'o', 'u');

I'm stuck at:

SELECT city FROM station WHERE SUBSTR(city,1, 1) IN ('a', 'e', 'i', 'o', 'u');

?

list all cities with names starting for 'a', 'e', 'i', 'o' or 'u' — Qbik, Feb 04 '17 at 21:32
ok, SQL Server and MySQL don't take into account if letters is capital or not — Qbik, Feb 05 '17 at 09:05
You may want to look at [Case insensitive searching in Oracle](http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle). — Álvaro González, Feb 05 '17 at 09:13

score 1 · Accepted Answer · answered Feb 04 '17 at 21:34

1

Your query is perfectly OK provided that the cities you are interested in start with lowercase letters. If that's not the case you may want to use LOWER function:

SELECT city FROM station WHERE LOWER(SUBSTR(city,1, 1)) IN ('a', 'e', 'i', 'o', 'u');

answered Feb 04 '17 at 21:34

Marcin Wroblewski

3,461
17
26

score 1 · Answer 2 · answered Feb 04 '17 at 21:40

If you want a version that works in all three databases you can use like:

SELECT s.city
FROM station s
WHERE LOWER(s.city) LIKE 'a%' OR
      LOWER(s.city) LIKE 'e%' OR
      LOWER(s.city) LIKE 'i%' OR
      LOWER(s.city) LIKE 'o%' OR
      LOWER(s.city) LIKE 'u%';

Is value on the list of values? (Oracle SQL)

2 Answers2