SQL query to check if a name begins and ends with a vowel

Question

I want to query the list of CITY names from the table STATION id  city  longitude  latitude  which have vowels as both their first and last characters  The result cannot contain duplicates   For this is I wrote a query like WHERE NAME LIKE  a   that had 25 conditions  each vowel for every other vowel  which is quite unwieldy  Is there a better way to do it

User · Answer

Use a regular expression   WHERE name REGEXP    aeiou    aeiou        and   anchor the match to the beginning and end of the value   In my test  this won t use an index on the name column  so it will need to perform a full scan  as would  WHERE name LIKE  a a  OR name LIKE  a e        I think to make it use an index you d need to use a union of queries that each test the first letter   SELECT   FROM table WHERE name LIKE  a   AND name REGEXP   aeiou    UNION SELECT   FROM table WHERE name LIKE  e   AND name REGEXP   aeiou    UNION SELECT   FROM table WHERE name LIKE  i   AND name REGEXP   aeiou    UNION SELECT   FROM table WHERE name LIKE  o   AND name REGEXP   aeiou    UNION SELECT   FROM table WHERE name LIKE  u   AND name REGEXP   aeiou

User · Answer

In MSSQL  this could be the way   select distinct city from station  where right city 1  in   a    e    i    o   u   and left city 1  in   a    e    i    o   u

User · Answer

Try the following   select distinct city  from station  where city like    aeuio  and city like   aeuio    Order by City

User · Answer

Try the following   select distinct city from station where city REGEXP    aeiou   and city REGEXP   aeiou

User · Answer

Select distinct city  from station where city RLIKE    aeiouAEIOU    Very simple answer

User · Answer

select distinct city  from STATION  where lower substr city  -1   in   a   e   i   o   u          and lower substr city  1 1   in   a   e   i   o   u

User · Answer

The below one worked for me in MySQL   SELECT DISTINCT CITY FROM STATION WHERE SUBSTR CITY 1 1  IN   A   E   I   O   U   AND SUBSTR CITY -1 1  in   A   E   I   O   U

User · Answer

SELECT DISTINCT city FROM   station WHERE  city RLIKE     aeiouAEIOU  OR city RLIKE   aeiouAEIOU

User · Answer

My simple solution      SELECT DISTINCT CITY  FROM STATION  WHERE CITY  LIKE   a e i o u   a e i o u

User · Answer

You can use LEFT   and RIGHT   functions  Left CITY 1  will get the first character of CITY from left  Right CITY 1  will get the first character of CITY from right  last character of CITY    DISTINCT is used to remove duplicates  To make the comparison case-insensitive  we will use the LOWER   function   SELECT DISTINCT CITY FROM STATION WHERE LOWER LEFT CITY 1   IN   a    e    i    o    u   AND       LOWER RIGHT CITY 1   IN   a    e    i    o    u

User · Answer

Worked for me using simple left  right functions in MS SQL select city from station where left city 1  in   a   e   i   o   u   and right city 1  in   a   e   i   o   u

User · Answer

The below query will do for Orale DB   select distinct city  from station where upper substr city  1 1   in   A   E   I   O   U   and upper substr city  length city  1   in   A   E   I   O   U

User · Answer

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     AND  CITY LIKE   a       OR CITY LIKE   e      OR CITY LIKE   i      OR CITY LIKE   o      OR CITY LIKE   u

User · Answer

You may try this      select city     from station where SUBSTRING city 1 1  in   A   E   I   O   U   and      SUBSTRING city -1 1  in   A   E   I   O   U

User · Answer

Both of the statements below work in Microsoft SQL SERVER  SELECT DISTINCT     city FROM     station WHERE     SUBSTRING lower CITY   1  1  IN   a    e    i    o    u       AND SUBSTRING lower CITY   LEN CITY   1  IN   a    e    i    o    u     SELECT DISTINCT     City FROM     Station WHERE     City LIKE   A  E  O  U  I   A  E  O  U  I   ORDER BY     City

User · Answer

You could substring the first and last character and compare it with IN keyword   WHERE SUBSTRING NAME 1 1  IN  a e i o u  AND SUBSTRING NAME -1  IN  a e i o u

User · Answer

For MS access or MYSQL server  SELECT city FROM station WHERE City LIKE   aeiou   and City LIKE    aeiou

User · Answer

in Microsoft SQL server you can achieve this from below query   SELECT distinct City FROM STATION WHERE City LIKE   AEIOU   AEIOU     Or  SELECT distinct City FROM STATION WHERE City LIKE   A E I O U   A E I O U     Update --Added Oracle Query     --Way 1 --It should work in all Oracle versions   SELECT DISTINCT CITY FROM STATION WHERE REGEXP LIKE LOWER CITY      aeiou    and  REGEXP LIKE LOWER CITY     aeiou           --Way 2 --it may fail in some versions of Oracle   SELECT DISTINCT CITY FROM STATION WHERE REGEXP LIKE LOWER CITY      aeiou    aeiou          --Way 3 --it may fail in some versions of Oracle   SELECT DISTINCT CITY FROM STATION WHERE REGEXP LIKE CITY     aeiou    aeiou     i

User · Answer

In Oracle   SELECT DISTINCT city  FROM station  WHERE SUBSTR lower CITY  1 1  IN   a   e   i   o   u   AND SUBSTR lower CITY  -1  IN   a   e   i   o   u

User · Answer

I hope this will help select distinct city from station where lower substring city 1 1   in   a   e   i   o   u   and lower substring city length city  length city    in   a   e   i   o   u

User · Answer

you can also do a hard code like this  where you are checking each and every case possible  it s easy to understand for beginners   SELECT DISTINCT CITY  FROM STATION WHERE CITY LIKE  A A  OR CITY LIKE  E E  OR CITY LIKE  I I  OR CITY LIKE  O O  OR CITY LIKE  U U  OR CITY LIKE  A E  OR CITY LIKE  A I  OR CITY LIKE  A O  OR  CITY LIKE  A U  OR CITY LIKE  E A  OR CITY LIKE  E I  OR CITY LIKE  E O  OR  CITY LIKE  E U  OR CITY LIKE  I A  OR CITY LIKE  I E  OR CITY LIKE  I O  OR  CITY LIKE  I U  OR CITY LIKE  O A  OR CITY LIKE  O E  OR CITY LIKE  O I  OR  CITY LIKE  O U  OR CITY LIKE  U A  OR CITY LIKE  U E  OR CITY LIKE  U I  OR  CITY LIKE  U O

User · Answer

You can use the following regular expression and invert the result      aeiou    aeiou     This works even if the input consists of a single character  It should work across different regex engines   MySQL  SELECT city FROM       SELECT  xx  AS city UNION     SELECT  ax          UNION     SELECT  xa          UNION     SELECT  aa          UNION     SELECT  x           UNION     SELECT  a    AS station WHERE NOT city REGEXP     aeiou    aeiou      PostgreSQL  WHERE NOT city       aeiou    aeiou      Oracle  WHERE NOT REGEXP LIKE city      aeiou    aeiou        SQL Server  No regular expression support  Use LIKE clause with square brackets   WHERE city LIKE   aeiou    AND city LIKE    aeiou

User · Answer

Try this below code   SELECT DISTINCT CITY FROM   STATIOn WHERE  city RLIKE    aeiouAEIOU     aeiouAEIOU

User · Answer

You could use a regular expression   SELECT DISTINCT city FROM   station WHERE  city RLIKE    aeiouAEIOU    aeiouAEIOU

User · Answer

For oracle    SELECT DISTINCT city FROM   station WHERE REGEXP LIKE city     aeiou    aeiou     i

User · Answer

Try this for beginning with vowel  Oracle   select distinct  field  from  tablename  where SUBSTR  sort field  1 1  IN  A   E   I   O   U   Order by  Sort Field

User · Answer

You can try one simple solution for MySQL  SELECT DISTINCT city FROM station WHERE city REGEXP  quot   aeiou    aeiou   quot

[mysql] SQL query to check if a name begins and ends with a vowel

Examples related to mysql

Examples related to sql

Examples related to select