How can I query names using regular expressions? How could I search for a specific pattern in a column?

For example, I like to get all user names that start with either letter A or B.

(btw: this is tagged oracle, but it might be interesting in other RDBMS as well).
Your question doesn't require regular expressions:

WHERE name LIKE 'A%' OR name LIKE 'B%'


For Oracle, there's REGEXP_LIKE; Postresql, there's SIMILAR TO; mysql has REGEXP.  One thing to remember though is not all regular expression engines are the same, so just because they support regular expressions doesn't mean they necessarily support word boundry assertions, non-greedy quantifiers, or negative lookbehind assertions, etc.