Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION

Question

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION.

My answer/tried code is:

select city from station where REGEXP_LIKE(city,'[^aeiou]+');

But it doesn't seem to be correct.

Kindly help me with this.

Answer 1

I will share 2 answers in particular for PostgreSQL. The first query returns the cities that start with a vowel using ILIKE:

SELECT city 
FROM table  
WHERE city ILIKE 'A%' 
OR city ILIKE 'E%' 
OR city ILIKE 'I%' 
OR city ILIKE 'O%' 
OR city ILIKE 'U%';

The ILIKE operator is used to perform a case-insensitive match so searching capital vowels would automatically small letters as well.

However, this query is too long and redundant in my opinion. Is it possible to rewrite this query without so many conditions? Yes!

Let's use SIMILAR TO:

SELECT city
FROM patients
WHERE city SIMILAR TO '[aeiouAEIOU]%'
ORDER BY city ASC;

This will return all rows from the table where the city column starts with a vowel (i.e., 'a', 'E', 'i', 'O', or 'U'). The % symbol is used as a wildcard to match any number of characters.

Note that SIMILAR TO and LIKE are not the same in PostgreSQL.

SIMILAR TO is used to match a string against a pattern that can include regular expressions. For example, you can use SIMILAR TO to find all strings that start with a vowel by using the pattern '[AEIOU]%'.

LIKE is used to match a string against a pattern that does not include regular expressions. It is similar to the = operator, except that it allows you to use the % and _ wildcard characters to match any number of or a single character, respectively. For example, you can use LIKE to find all strings that start with a vowel by using the pattern 'A%'.

Answer 2

On MSSQL server:

SELECT DISTINCT(city) FROM station WHERE city LIKE '[a,e,i,o,u]%' ;

On MySQL:

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

Answer 3

We can use a regexp combined with REGEXP_LIKE

MYSQL

SELECT DISTINCT CITY 
FROM STATION
WHERE REGEXP_LIKE(city,'^[AEIOU]');

Answer 4

with cte as (
select CITY ,case when lower(substr(CITY,1,1)) in ('a','e','i','o','u') then 1 else 0 end as Rule2
    from STATION
)
select distinct CITY  from cte where Rule2=1;

Answer 5

SELECT DISTINCT CITY
 FROM STATION 
 WHERE CITY LIKE "a%" OR 
 CITY LIKE "e%"
 OR       CITY LIKE "i%"
 OR  CITY LIKE "o%"
 OR CITY LIKE "u%";

Answer 6

This can be solved by using substring and lower functions:

SELECT CITY FROM STATION WHERE LOWER(SUBSTR(CITY,1,1)) in ('a','e','i','o','u');

substring will find the first character of string and lower function make this first character lower. Once we find the lower first character then we can find that it is vowel or not by using IN operator.

Answer 7

All 3 works on MySQL

1. By using regular expression

SELECT DISTINCT(CITY)
FROM STATION 
WHERE CITY REGEXP '^[aeiou]';

2. By using OR

SELECT DISTINCT(CITY)
FROM STATION 
WHERE (CITY LIKE "A%") 
    OR (CITY LIKE "E%")
    OR (CITY LIKE "I%")
    OR (CITY LIKE "O%")
    OR (CITY LIKE "U%");

3. By using SUBSTR(string, start, length).
   SUBSTR is 1 based indexing.
   Therefore, SUBSTR(CITY, 1, 1) means extracting substring at position 1 of length 1 from CITY column.

SELECT DISTINCT(CITY)
FROM STATION 
WHERE SUBSTR(CITY, 1, 1) IN ('A', 'E', 'I', 'O', 'U');

Answer 8

select distinct city from station where city ~*'^(a|e|i|o|u)[a-z]$';

This is for Postgres

Answer 9

This below solution is for MySQL DATABASE

 SELECT DISTINCT CITY FROM STATION WHERE CITY LIKE 'a%' OR CITY LIKE 'e%' OR CITY LIKE 'i%' OR CITY LIKE 'o%' OR CITY LIKE 'u%';

Answer 10

For Mysql, this worked for me -

  select DISTINCT(CITY) from STATION 
  where CITY REGEXP '^[aeiou].*';

Answer 11

select distinct city from station where regexp_like(city, ‘^[aeiou]’, ‘i’)

Also, for ending with a vowel '[aeiou]$' and for starting and ending with a vowel '^[aeiou]$' $- for ending with, ^ for starting with, i- for case insensitivity, will select cities ending with uppercase as well.

Answer 12

SELECT DISTINCT CITY FROM STATION WHERE SUBSTR(CITY,1,1) IN ('A','E','I','O','U')

Answer 13

Another way to get output

select distinct CITY from STATION where left(city,1) in ('a', 'e', 'i', 'o', 'u')

Answer 14

SELECT distinct(city)
FROM station
WHERE city LIKE '[a,e,i,o,u]%'

Answer 15

Try with MySQL solution:

select distinct CITY from STATION where substr(CITY,1,1) in ('a','e','i','o','u');

Here "distinct" will solve the problem of duplicate value and "substring" function extract substring from string . Substring also contain start & length . For more details follow the link :- https://www.w3schools.com/sql/func_mysql_substr.asp

Answer 16

That worked for me

SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[aeiou]'

Answer 17

use SUBSTR

select t.city from station t where lower(SUBSTR(city,1,1)) in ('a','e','i','o','u')

Answer 18

As BackSlash have already commented, you've written the wrong REGEXP_LIKE pattern and you should change it to '^[aeiou].+', or you can even ommit .+ from your pattern, as you're only interested in the first letter of your string (containing more than 1 character):

select city from station where REGEXP_LIKE(city,'^[aeiou]');

Example with test data

Beware that would only return stations that start with lowercase vowels! If you also want to include uppercase vowels than add them to your pattern:

select city from station where REGEXP_LIKE(city,'^[aeiouAEIOU]');

or specify inside REGEXP_LIKE call that inputted pattern is case-insensitive with an 'i' flag, like this:

select city from station where REGEXP_LIKE(city,'^[aeiou]', 'i');

Example with test data

Kudos to MT0 for helpful comment!

I hope we helped!