/regex rosetta stone/i

PostgreSQL

PostgreSQL ARE
About
Advanced regular expressions as used by the ~ operator and the regexp_ functions. By default a dot matches a newline and ^ and $ anchor at the ends of the text.
Verified on
PostgreSQL 18.7
Offsets are counted in code points.
Found in
PostgreSQL ~, regexp_match(), SIMILAR TO (a different syntax)
its own documentation
Flags
i ignore casen newlinew multilinex verbose
Character classes 9 of 26
SyntaxMatchesNotesTry
. Any characterAny single character. Whether that includes a newline depends on the dialect and its flags.

Matches a newline.

test
[abc]not[^abc] One of a set of characters test
[a-z] One of a range of characters test
[\]] Backslash escape inside a setInside a bracket expression a backslash takes the special meaning from the next character. Where this is missing, a backslash there is a member like any other. test
[\d] Class escape inside a setA class escape such as the one for a digit, used as a member of a bracket expression. test
[\D] Negated class escape inside a setA negated class escape, such as the one for anything but a digit, used as a member of a bracket expression. test
\d[[:digit:]]not\D Digit

ASCII digits only: 0 to 9.

test
\w[[:alnum:]_]not\W Word characterA letter, a digit or an underscore.

Unicode-aware: matches letters and digits of every script.

test
\s[[:space:]]not\S Whitespace

Also matches the no-break space.

test
n/a Horizontal whitespaceA space or a tab, but not a line break. not in PostgreSQL

Tried: \h does not compile.

who has it
n/a Vertical whitespaceA line break character of any kind. not in PostgreSQL

Tried: \v compiles, and matches as plain text or not at all.

who has it
n/a Any character except newlineNever matches a newline, whatever the flags. not in PostgreSQL

Tried: \N does not compile.

who has it
n/a Any line break sequenceOne line break, with CR LF taken as a single unit. not in PostgreSQL

Tried: \R does not compile.

who has it
n/a Alphabetic character (shorthand) not in PostgreSQL

Tried: \a compiles, and matches as plain text or not at all.

who has it
n/a Lowercase letter (shorthand) not in PostgreSQL

Tried: \l does not compile.

who has it
n/a Uppercase letter (shorthand) not in PostgreSQL

Tried: \u does not compile.

who has it
n/a Hexadecimal digit (shorthand) not in PostgreSQL

Tried: \h does not compile.

who has it
n/a Class intersectionCharacters that are in both classes. not in PostgreSQL

Tried: [a-z&&[^a-m]] compiles, and matches as plain text or not at all.

who has it
n/a Class subtractionCharacters in the first class that are not in the second. not in PostgreSQL

Tried: [a-z&&[^aeiou]] compiles, and matches as plain text or not at all.

who has it
n/a Head-of-word character (shorthand)A letter or the underscore: what a word may start with. not in PostgreSQL

Tried: \h does not compile.

who has it
n/a Octal digit (shorthand) not in PostgreSQL

Tried: \o does not compile.

who has it
n/a Identifier characterA character allowed in an identifier. In Vim the set comes from the 'isident' option. not in PostgreSQL

Tried: \i does not compile.

who has it
n/a Keyword characterA character allowed in a keyword. In Vim the set comes from the 'iskeyword' option. not in PostgreSQL

Tried: \k does not compile.

who has it
n/a File name characterA character allowed in a file name. In Vim the set comes from the 'isfname' option. not in PostgreSQL

Tried: \f compiles, and matches as plain text or not at all.

who has it
n/a Printable characterA character that is displayed as itself. In Vim the set comes from the 'isprint' option. not in PostgreSQL

Tried: \p does not compile.

who has it
n/a Any single byteOne code unit, even where that is part of a longer character, and even a newline. not in PostgreSQL

Tried: \C does not compile.

who has it
Anchors 8 of 14
SyntaxMatchesNotesTry
^ Start of the text or lineStart of the text. With a multiline flag, or always in some dialects, also the start of each line. test
$ End of the text or line

Matches only at the very end, not before a final newline.

test
\A Start of the textStart of the text only, whatever the flags. test
\Z End of the textThe very end of the text, whatever the flags. test
n/a End of the text, or before a final newline not in PostgreSQL

Tried: a\Z compiles, and matches as plain text or not at all.

who has it
\y Word boundaryThe position between a word character and a non-word character or an end of the text.

Unicode-aware: a letter outside ASCII is a word character.

test
\Y Not a word boundary test
\m[[:<:]] Start of a word test
\M[[:>:]] End of a word test
n/a Where the previous match endedThe position where the last match stopped, or the start of the text for the first match. not in PostgreSQL

Tried: \Ga does not compile.

who has it
n/a Reset the start of the matchEverything matched so far is required but left out of the reported match. not in PostgreSQL

Tried: foo\Kbar does not compile.

who has it
n/a Start of symbolBefore a run of word and symbol characters, such as an identifier with an underscore in it. not in PostgreSQL

Tried: \_<b compiles, and matches as plain text or not at all.

who has it
n/a End of symbol not in PostgreSQL

Tried: a\_> compiles, and matches as plain text or not at all.

who has it
n/a Cursor positionWhere the editor's cursor is in the buffer being searched. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it
Quantifiers 8 of 9
SyntaxMatchesNotesTry
X? Zero or one test
X* Zero or more test
X+ One or more test
X{N} Exactly N times test
X{N,} N or more times test
X{0,M} At most M times test
X{N,M} Between N and M times test
X*?X+?X?? Lazy quantifierRepeat as few times as possible instead of as many as possible. test
n/a Possessive quantifierRepeat as many times as possible and never give any of it back. not in PostgreSQL

Tried: a*+b does not compile.

who has it
Groups and alternation 5 of 16
SyntaxMatchesNotesTry
(X) Capture group test
(?:X) Non-capturing group test
n/a Named capture group not in PostgreSQL

Tried: (?<n>[0-9]+) does not compile.

who has it
X|Y Alternation

Takes the longest match among the alternatives (leftmost-longest).

test
\N Backreference by numberThe same text that capture group N matched. test
n/a Backreference by name not in PostgreSQL

Tried: (?<n>a)\k<n> does not compile.

who has it
n/a Backreference by relative positionThe group N groups back from here. not in PostgreSQL

Tried: (a)\g-1 does not compile.

who has it
n/a Atomic groupOnce the group has matched, the engine never tries a different way to match it. not in PostgreSQL

Tried: (?>a+)b does not compile.

who has it
n/a Branch reset groupEvery alternative numbers its capture groups from the same start. not in PostgreSQL

Tried: (?|(a)|(b)) does not compile.

who has it
n/a ConditionalMatch one pattern if a capture group took part, otherwise another. not in PostgreSQL

Tried: (<)?a(?(1)>|!) does not compile.

who has it
n/a Recurse into the whole pattern not in PostgreSQL

Tried: \((?:a|(?R))\) does not compile.

who has it
n/a Call a group as a subroutineMatch the pattern of capture group N again at this point, not the text it matched. not in PostgreSQL

Tried: (a|b)(?1) does not compile.

who has it
(?#TEXT) Comment test
n/a Explicitly numbered groupA capture group that is given its number, whatever its position. not in PostgreSQL

Tried: (?<2>a) does not compile.

who has it
n/a Call an earlier group by relative positionMatch the pattern of the Nth group before this point again. not in PostgreSQL

Tried: (a|b)(?-1) does not compile.

who has it
n/a Call a later group by relative positionMatch the pattern of the Nth group after this point here as well. not in PostgreSQL

Tried: (?+1)(a|b) does not compile.

who has it
Lookaround 4 of 4
SyntaxMatchesNotesTry
(?=X) LookaheadWhat follows must match X, but is not part of the match. test
(?!X) Negative lookahead test
(?<=X) LookbehindWhat comes before must match X, but is not part of the match.

The lookbehind may have unbounded length.

test
(?<!X) Negative lookbehind test
Escape sequences 14 of 16
SyntaxMatchesNotesTry
\. Escape a special characterTake the next character literally. test
n/a Quote a run of textTake everything between the markers literally. not in PostgreSQL

Tried: \Qa.b\E does not compile.

who has it
\t Tab test
\n Newline test
\r Carriage return test
\f Form feed test
\v Vertical tab test
\a Alert (bell) test
\b[\b] Backspace test
\e Escape character test
\cC Control characterThe control character typed as Ctrl plus the letter. test
\OOO Character by octal code test
\xHH Character by two-digit hex code test
\uHHHH\UHHHHHHHH Character by Unicode code point test
n/a Character by Unicode name not in PostgreSQL

Tried: \N{DIGIT ONE} does not compile.

who has it
\0 NUL characterThe character with code 0.

\0 followed by one or two octal digits is read as an octal escape instead. The character types cannot store code zero and a string constant cannot contain it, so no text subject holds a NUL for \0 to match.

doc Not run on an engine: taken from the documentation.

Flags and modes 6 of 14
SyntaxMatchesNotesTry
flag i(?i) Ignore case test
flag w(?w)(?n)(?m) MultilineThe line anchors also match at the start and end of every line.

Flag w changes only the anchors. Flag n, also written m, changes the anchors and stops the dot and negated brackets from matching a newline.

test
(?s) Dot matches newline partial

On by default: the dot matches a newline unless flag n or p is set. (?s) only states the default.

No flag is needed.

Flag n turns it off.

test
flag x(?x) Free spacingWhitespace in the pattern is ignored, and a # starts a comment. test
n/a Swap greedy and lazy not in PostgreSQL

Tried: (?U)a.*c does not compile.

who has it
(?FLAGS) Turn a mode on inside the patternApplies from this point to the end of the pattern or the enclosing group. test
n/a Turn a mode on in the middle of the patternThe mode applies from where the flag stands, not to what comes before it. Some dialects accept an inline flag only at the very start of the pattern. not in PostgreSQL

Tried: a(?i)b does not compile.

who has it
n/a Turn a mode on for one group not in PostgreSQL

Tried: (?i:a)b does not compile.

who has it
n/a Turn a mode off inside the pattern not in PostgreSQL

Tried: (?i)a(?-i)b does not compile.

who has it
n/a ASCII-only classesMakes the shorthand classes such as the digit class match ASCII characters only. not in PostgreSQL

Tried: (?a)\d does not compile.

who has it
n/a Unicode classesMakes the shorthand classes such as the digit class match by Unicode property. not in PostgreSQL

Tried: (?u)\d does not compile.

who has it
n/a No automatic capturePlain parentheses group without capturing; only named groups capture. not in PostgreSQL

Tried: (?n)(a)(?<x>b) does not compile.

who has it
TEXT ~ PATTERN COLLATE "de_DE" Locale-dependent classesClasses and case folding follow the current locale. partial

There is no mode letter: classes such as [[:alpha:]] and \w always follow the collation of the operator or function, by default the database LC_CTYPE, and a COLLATE clause picks another. Nondeterministic collations are not supported.

doc Not run on an engine: taken from the documentation.

n/a Native character set rulesPerl's rules from before 5.14: the pattern and the text decide between native and Unicode rules. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it
POSIX named classes 1 of 1
SyntaxMatchesNotesTry
[[:NAME:]]not[^[:NAME:]] POSIX named classA named set of characters, written inside a bracket expression.

14 names: alnum alpha ascii blank cntrl digit graph lower print punct space upper word xdigit

Not here: nonascii multibyte

test
POSIX collating symbols 2 of 2
SyntaxMatchesNotesTry
[[=X=]] Equivalence classEvery character that collates the same as X in the current locale. test
[[.NAME.]] Collating symbol by nameA single collating element, written by its POSIX name inside a bracket expression.

127 names: SOH STX ETX EOT ENQ ACK alert backspace tab newline vertical-tab form-feed carriage-return SO SI DLE DC1 DC2 DC3 DC4 NAK SYN ETB CAN EM SUB ESC IS4 IS3 IS2 IS1 space exclamation-mark quotation-mark number-sign dollar-sign percent-sign ampersand apostrophe left-parenthesis right-parenthesis asterisk plus-sign comma hyphen period slash zero one two three four five six seven eight nine colon semicolon less-than-sign equals-sign greater-than-sign question-mark commercial-at A B C D E F G H I J K L M N O P Q R S T U V W X Y Z left-square-bracket backslash right-square-bracket circumflex underscore grave-accent a b c d e f g h i j k l m n o p q r s t u v w x y z left-curly-bracket vertical-line right-curly-bracket tilde DEL

test
Unicode properties 0 of 4
SyntaxMatchesNotesTry
n/a Unicode general category not in PostgreSQL

Tried: \p{L} does not compile.

who has it
n/a Unicode script not in PostgreSQL

Tried: \p{Latin} does not compile.

who has it
n/a Extended grapheme clusterOne character as a reader sees it: a base character with its combining marks. not in PostgreSQL

Tried: \X does not compile.

who has it
n/a Unicode text boundaryA boundary as Unicode's segmentation rules define it, which is not the same as a boundary between word and non-word characters. not in PostgreSQL

Tried: e\b{gcb} compiles, and matches as plain text or not at all.

who has it
Syntax table classes 0 of 2
SyntaxMatchesNotesTry
n/a Syntax classA character with the given role in the syntax table: whitespace, word, symbol, punctuation, a bracket. not in PostgreSQL

Tried: \s- compiles, and matches as plain text or not at all.

who has it
n/a Character categoryA character in one of the editor's character categories, most of which are scripts. not in PostgreSQL

Tried: \ca compiles, and matches as plain text or not at all.

who has it
Host language 8 of 15
SyntaxMatchesNotesTry
'PATTERN'$$PATTERN$$E'PATTERN' Pattern in source codeHow a pattern is written where it is used: a regex literal with its delimiters, or the kind of string the documentation recommends.

No regex literal; a pattern is a string constant. In '...' a single quote is doubled and backslashes are literal while standard_conforming_strings is on (the default); in E'...' every backslash is doubled; in $$...$$ nothing is escaped.

doc Not run on an engine: taken from the documentation.

regexp_like(TEXT, PATTERN, FLAGS)regexp_replace(TEXT, PATTERN, REPLACEMENT, FLAGS)TEXT ~* PATTERN Flags outside the patternWhere flags such as case-insensitive go when they are not written inside the pattern.

FLAGS is a text string of option letters such as 'i', accepted as an optional argument by every regexp_ function. The ~* and !~* operators are the case-insensitive forms of ~ and !~.

doc Not run on an engine: taken from the documentation.

n/a Compile once and reuseBuild a pattern object and keep it. not in PostgreSQL

Every operator and function takes the pattern as text; there is no compiled-pattern type.

doc Not run on an engine: taken from the documentation.

who has it
TEXT ~ PATTERNregexp_like(TEXT, PATTERN)regexp_substr(TEXT, PATTERN)regexp_match(TEXT, PATTERN) Search a textTest whether the pattern matches anywhere in a text, or get the first match.

Not anchored: the pattern may match anywhere in TEXT. ~ and regexp_like return a boolean, regexp_substr the first matching substring or NULL, regexp_match a text array or NULL.

doc Not run on an engine: taken from the documentation.

regexp_matches(TEXT, PATTERN, 'g') Every matchGet all the matches that do not overlap.

Returns one row per match, each a text array. Without the g flag it returns at most one row.

doc Not run on an engine: taken from the documentation.

regexp_replace(TEXT, PATTERN, REPLACEMENT)regexp_replace(TEXT, PATTERN, REPLACEMENT, 'g') Replace matches

The plain form replaces the first match only; add the g flag to replace all. The form regexp_replace(TEXT, PATTERN, REPLACEMENT, start, N) replaces the N-th match, or all when N is 0.

doc Not run on an engine: taken from the documentation.

(regexp_match(TEXT, PATTERN))[NUM]regexp_substr(TEXT, PATTERN, 1, 1, '', NUM) Captured group, in codeRead what a group captured after a match. partial

By number only; the pattern syntax has no named groups. With no parenthesized subexpression in the pattern, element 1 of the regexp_match array is the whole match.

doc Not run on an engine: taken from the documentation.

\NUM Captured group, in replacement text partial

By number only, and only \1 through \9; there are no named groups. The reference is a single digit and the documentation defines no braced or multi-digit form.

doc Not run on an engine: taken from the documentation.

\& Whole match, in replacement text

A literal backslash in the replacement is written \\.

doc Not run on an engine: taken from the documentation.

n/a Match once until resetA match operator that succeeds one time only, until it is reset. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it
n/a Keep the position after a failed matchIn a repeated search, a failed match does not send the next one back to the start. not in PostgreSQL

No search position is kept between calls. regexp_count, regexp_instr, regexp_replace and regexp_substr take an explicit start character index instead.

doc Not run on an engine: taken from the documentation.

who has it
n/a Keep the matched text for laterAsk for the matched text, and the text around it, to be saved. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it
n/a Interpolate and compile only onceA pattern literal with variables in it is built the first time and never again. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it
n/a Code inside a patternHost-language code that runs when the match reaches it. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it
n/a Pattern computed during the matchCode inside the pattern whose result is matched as a pattern at that point. not in PostgreSQL

doc Not run on an engine: taken from the documentation.

who has it

Every row was run on the engine named above, except the rows marked doc: those describe something no regex engine can run by itself, such as the syntax of the host language, and are taken from the documentation they link to. For a row marked "not in PostgreSQL", the spelling other dialects use was tried and the line starting "Tried" says what it did here; that shows the spelling does not work, not that no other spelling exists.