I have username in this pattern
ref_2_34_aaa_dos
ref_2_34_bbb_dos
How can I use regexp_like for this?
SELECT username FROM all_users WHERE regexp_like(username, '^ref_2_34_[:alpha:]_dos$')
does not work. Nor can I use ESCAPE '\' with regexp_like. it would give a syntax error.
You need to put the POSIX character class [:alpha:] into a bracket expression (i.e. [...]) and apply a + quantifier to it:
regexp_like(username, '^ref_2_34_[[:alpha:]]+_dos$')
The + quantifier means there can be 1 or more letters between the last but one and last underscores.
If your string may have no user name at that location (it is empty), and you want to get those entries too, you would need to replace the plus with the * quantifier that matches zero or more occurrences of the quantified subpattern.
Since comments require some more clarifications, here is some bracket expression and POSIX character class reference.
Bracket expressions
Matches any single character in the list within the brackets. The following operators are allowed within the list, but other metacharacters included are treated as literals:
- Range operator:
-- POSIX character class:
[: :]- POSIX collation element:
[. .]- POSIX character equivalence class:
[= =]A dash (
-) is a literal when it occurs first or last in the list, or as an ending range point in a range expression, as in[#--]. A right bracket (]) is treated as a literal if it occurs first in the list.
POSIX Character Class (can be a part of the bracket expression):
[:class:]- Matches any character belonging to the specified POSIX character class. You can use this operator to search for characters with specific formatting such as uppercase characters, or you can search for special characters such as digits or punctuation characters. The full set of POSIX character classes is supported. ... The expression[[:upper:]]+searches for one or more consecutive uppercase characters.
Bracket expressions can be considered a kind of a "container" construct for multiple atoms, that, as a whole regex unit, matches some class of characters you defined. If you need to match a <, or >, or letters, you may combine them into 1 bracket expression [<>[:alpha:]]. To match zero or more of <, > or letters, add a * quantifier after ]: [<>[:alpha:]]*.
Or, to imitate a trailing word boundary, one might use [^_[:alnum:]] (say, in a ($|[^_[:alnum:]]) pattern) that matches any character but a _, digits and letters ([:alnum:] matches alphanumerical symbols).
[[:alpha:]] looks like some sort of voodoo, but it isn't. In fact, it's not one set of double-square-brackets; the two pairs are separate and mean different things. The outer ones mean "match list" and the inner ones with the :...: are a character class. You could write the same as [a-zA-Z] and it would mean the same thing. [:alpha:] means the same thing as a-zA-Z, namely any one (sigle) alpha character, upper or lower case.
regexp_like(username, '^ref_2_34_[[:alpha:]]+_dos$')