Alternation group with two alternatives matching entire string

Question

I working on a regular expression which returns 1 only in case given value is A or B.

I used

select 'A' REGEXP '^A|B$'

The '|' symbol is behaving like "or", but the result is not as expected with AB:

select 'AB' REGEXP '^A|B$' = 1

while I expect no match here.

Answer 1

Your current solution matches

• ^A - A at the start of the string
• | - or
• B$ - B at the end of the string.

The REGEXP operator can return partial matches (unlike LIKE operator that requires the wildcard pattern to match the whole string), and thus can match ABC and CAB. See the regex demo.

You may use

select 'A' REGEXP '^(A|B)$'

The A|B is inside a grouping construct here and ^ and $ both modify the A and B branch. See this regex demo.

If these A and B are single chars, use a [AB] bracket expression:

'^[AB]$'

where [AB] matches A or B, and the ^ / $ anchors modify each letter inside the brackets.

See this regex demo.