CODEWITHSUNDEEP
mysqlregex
Anuj Kumar Soni
Anuj Kumar Soni

Reputation: 192

Regex Error in MYSQL

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.

Upvotes: 2

Views: 873

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626689

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.

Upvotes: 1

Er Amit Anand
Er Amit Anand

Reputation: 50

^ : 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.

Upvotes: 0

Related Questions