I want to select cities starting with a,e, i,o,u and ending with a,e, i,o,u in MySQL.(Case not matters)
Query1
SELECT CITY FROM STATION WHERE CITY REGEXP '^[AEIOU]' and CITY REGEXP '[AEIOU]$';
Query2
SELECT CITY FROM STATION WHERE CITY REGEXP '^[AEIOU]*[AEIOU]$';
Why Query2 is giving me an error although Query1 is correct.
With your first query, you only fetch entries that start or end with vowels. The second one only matches entries that start with 0 or more vowels and end with a vowel (so, you will get results like a or Aou only).
You might try using
SELECT CITY FROM STATION WHERE CITY REGEXP '^[AEIOU].*[AEIOU]$'
^^
The .* pattern matches any 0+ chars, as many as possible, so it will matching any string that starts AND ends with a vowel.
However, WHERE CITY REGEXP '^[AEIOU]' and CITY REGEXP '[AEIOU]$' fetches entries only consisting of 1 vowel, and the above will not match a record like A (one-vowel string). To match those use an optional group:
SELECT CITY FROM STATION WHERE CITY REGEXP '^[AEIOU](.*[AEIOU])?$'
^ ^^
Here, (...)? is a capturing group (MySQL regex does not support non-capturing ones) that matches a sequence of patterns 1 or 0 times (due to the ? quantifier).
A couple of notes on the regex:
^[AEIOU].*[AEIOU]$ - matches a whole string that starts and ends with a vowel in a case insensitive way (REGEXP is not case sensitive, except when used with binary strings)
^ - matches the start of input[AEIOU] - a single vowel from the set.* - any 0+ chars as many as possible (POSIX regex used in MySQL does not support lazy quantifiers, and . matches any chars, even line break chars, too) [AEIOU] - a vowel$ - end of input.
LIKE use % rather than *,[AEIOUaeiou]^ : Match the beginning of a string.and $ : Match the end of a string. so you can try with above both regex and also use % , may be helpful.
% char used with REGEXP will only match a literal %.