'SQL query city names starting And ending with vowels

i wrote this code (db2) and it works just fine, but i'm wondering, is there a shorter way to write this?

Select Distinct city
From   station
Where  city Like 'A%a'
       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%e'
       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%i'
       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%o'
       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'
       Or city Like 'U%u';
sqlflutterdart


Solution 1:[1]

I am not a DB2 expert but this should be fairly portable:

WHERE LEFT(city,1) IN ('A', 'E', 'I', 'O', 'U')
  AND RIGHT(city,1) IN ('a', 'e', 'i', 'o', 'u')

You may want to normalize it all to upper case to avoid problems with cities that for some reason start with a lower case letter or end with an upper case letter.

WHERE UPPER(LEFT(city,1)) IN ('A', 'E', 'I', 'O', 'U')
  AND LOWER(RIGHT(city,1)) IN ('a', 'e', 'i', 'o', 'u')

Solution 2:[2]

You can use REGEXP in MySQL to operate Regular Expression

SELECT DISTINCT city 
FROM station 
WHERE city REGEXP '^[aeiou].*[aeiou]$';

For people who don't familiar with Regular Expression

^[aeiou]    // Start with a vowel
.*          // Any characters at any times
[aeiou]$    // End with a vowel

Solution 3:[3]

I'm not an expert in DB2, but I think you can use a regular expression in your WHERE LIKE.

Check the documentation: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061494.html

Example:
Select Distinct city
From   station
Where  REGEXP_LIKE(city,'[aeiou].*[aeiou]')

Solution 4:[4]

Use regular expressions in your LIKE clause. Look at the example here Using RegEx in SQL Server

In Sql server your LIKE clause might look like this.

LIKE '[AEIOU]%[aeiou]'

In DB2 the syntax is slightly different. Look few examples down on this page.

https://www.ibm.com/developerworks/data/library/techarticle/0301stolze/0301stolze.html

Solution 5:[5]

You could use substr like this:

SELECT DISTINCT CITY 
FROM STATION WHERE SUBSTR(CITY,1,1) IN('A','E','I','O','U','a','e','i','o','u') 
and SUBSTR(CITY,-1,1) IN('A','E','I','O','U','a','e','i','o','u');

Solution 6:[6]

SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[^aeiouAEIOU].*');

Solution 7:[7]

Try the following:(oracle) SELECT DISTINCT CITY FROM STATION WHERE REGEXP_LIKE(CITY,'^[^aeiouAEIOU].*');

Solution 8:[8]

This will be answerable in SQL Server in 3 lines with an AND clause-

SELECT DISTINCT CITY FROM *TableName* 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');

...

Also, you can use Regex in case of MySQL as-

SELECT DISTINCT city
FROM   station
WHERE  city RLIKE '^[aeiouAEIOU].*[aeiouAEIOU]$'

Solution 9:[9]

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

Solution 10:[10]

For Oracle, you can write this-

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

Solution 11:[11]

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

Solution 12:[12]

Maybe this code will work.

Select Distinct City from station
Where city Like '[aeiou]%[aeiou]'

This worked for me.

Solution 13:[13]

In MSSQL server code working fine

select distinct city from station where city like '[aeiouAEIOU]%[aeiouAEIOU]';

Solution 14:[14]

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

Solution 15:[15]

As a beginner, this is how I solved it:

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 16:[16]

class PersonDetails extends StatefulWidget {
      final String id;
     PersonDetails({
       required this.id,
      });

      @override
      _PersonDetailsState createState() => _PersonDetailsState();
      }

     class _PersonDetailsState extends State<PersonDetails> {
     @override
    void initState() {
        person=getPersonDetails();
     super.initState();
   }

      late Future<Person> person;

       @override
       Widget build(BuildContext context) {
             return Scaffold(
         appBar: AppBar(
             title: Text(person.id.toString()),
            ),
           body: Container(child: Text('you are here')),
         );
        }

     Future<Person> getPersonDetails() async {
     final params = {
     "api_key": "df1555a226ba2b6d6c15c425a626e6bf",
     "language": "en-US",
           };

        Uri url = Uri.https('api.themoviedb.org', 
       '/3/person/${widget.id}', 
      params);
      http.Response res = await http.get(url);

    if(res.statuscode==200){
       Map<String,dynamic>map=jsonDecode(res.body);
       return Person.fromjson(map);
      }else{
      return throw Exception("error occur");
      }}

in your code person variable is initialising late because when statefull widget initialize in the init state you are calling the api which take time to fetch data from the server and then that variable is initialized. that is the correct way of doing that or you can make Person nullable when data is fetched from the api you can setstate the variable

Related Questions