CODEWITHSUNDEEP
mysqlsqlpostgresql
Hamberfim
Hamberfim

Reputation: 35

Is there a Shorter way - SQL - multiple LIKE and OR

Is there away to shorten a SQL statement like this? If so what would it be in MySQL and also in PostgreSQL?

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

Upvotes: 1

Views: 389

Answers (1)

spencer7593
spencer7593

Reputation: 108480

With MySQL, we could use a regular expression, something like this:

WHERE city REGEXP '^[AEIOU].*[aeiou]$'

Not to dive too deep into regular expressions, but as an aid to deciphering that:

  • the caret ^ matches the beginning of the string

  • the next character has to be one of the characters in the set in the square brackets A, E, I, O or U

  • followed by any number of any character, the dot . matches any character, the * is repetition, zero, one or more

  • the last character has to match one of characters in the square brackets a, e, i, o, u because the dollar sign $ matches the end of the string

For testing, use a SELECT statement 

 SELECT t.city
      , t.city REGEXP '^[AEIOU].*[aeiou]$'
   FROM ( SELECT 'A' AS city 
          UNION ALL SELECT 'Aa' 
          UNION ALL SELECT 'Abba' 
          UNION ALL SELECT 'a' 
        ) t

As Gordon points out in a comment, the same approach using a regular expression comparison will work in PostgreSQL. But there syntax is a a little different, the comparison operation is a tilde ~ character, in place of MySQL REGEXP or RLIKE keyword.

WHERE city      ~ '^[AEIOU].*[aeiou]$'

https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Upvotes: 6

Related Questions