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

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.

sqlregexoracleregexp-like


Solution 1:[1]

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!

Solution 2:[2]

use SUBSTR

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

Solution 3:[3]

Another way to get output

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

Solution 4:[4]

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');

Solution 5:[5]

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

Solution 6:[6]

All 3 works on MySQL

  1. By using regular expression
SELECT DISTINCT(CITY)
FROM STATION 
WHERE CITY REGEXP '^[aeiou]';
  1. 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%");
  1. 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');

Solution 7:[7]

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.

Solution 8:[8]

That worked for me

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

Solution 9:[9]

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

Solution 10:[10]

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.

Solution 11:[11]

For Mysql, this worked for me -

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

Solution 12:[12]

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%';

Solution 13:[13]

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

This is for Postgres

Solution 14:[14]

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%";

Solution 15:[15]

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;

Solution 16:[16]

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

Solution 17:[17]

We can use a regexp combined with REGEXP_LIKE

MYSQL

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

Related Questions