PostgreSQL Regular Expression

When using regular expressions in PostgreSQL, you must use the keyword "~". To indicate that the content before the keyword needs to match the regular expression. If the matching rules do not need to be case sensitive, you can use the combination keyword "~ *";

On the contrary, to query records that do not match this expression, you only need to add the negative keyword "!" before this keyword. You can. If the regular expression contains escape characters, you must add the keyword "E" before the expression ".

For example:

[SQL]View plaincop

1. Select * fromUserWhere email ~ '^ [A-H]' -- match record with email address starting with A-H
2. Select * fromUserWhere email ~ * '^ [A-H]' -- match the record whose email address starts with A-H and a-h

The regular expression matching formula is generic. You can refer to the following table.

Character	Description
\	Mark the next character as a special character, a literal character, a back reference, or an octal escape character. For example, 'n' matches the character "N ". '\ N' matches a line break. The sequence '\' matches "\" and "\ (" matches "(".
^	Matches the start position of the input string. IfRegexpObjectMultilineProperty, ^ matches the position after '\ n' or' \ R.
$	Matches the end position of the input string. IfRegexpObjectMultilineAttribute, $ also matches the position before '\ n' or' \ R.
*	Matches the previous subexpression zero or multiple times. For example, Zo * can match "Z" and "Zoo ". * Is equivalent to {0 ,}.
+	Match the previous subexpression once or multiple times. For example, 'Zo + 'can match "zo" and "Zoo", but cannot match "Z ". + Is equivalent to {1 ,}.
?	Match the previous subexpression zero or once. For example, "Do (ES )? "Can match" do "in" do "or" does ".? It is equivalent to {0, 1 }.
{N}	NIs a non-negative integer. MatchedNTimes. For example, 'O {2} 'cannot match 'O' in "Bob", but can match two o in "food.
{N,}	NIs a non-negative integer. At least matchNTimes. For example, 'O {2,} 'cannot match 'O' in "Bob", but can match all o in "foooood. 'O {1,} 'is equivalent to 'o + '. 'O {0,} 'is equivalent to 'o *'.
{N,M}	MAndNAll are non-negative integers, whereN<=M. Least matchNTimes and most matchingMTimes. Liu, "O {1, 3}" will match the first three o in "fooooood. 'O {0, 1} 'is equivalent to 'o? '. Note that there must be no space between a comma and two numbers.
?	When this character is followed by any other delimiter (*, + ,?, {N},{N,},{N,M}) The matching mode is not greedy. The non-Greedy mode matches as few searched strings as possible, while the default greedy mode matches as many searched strings as possible. For example, for strings "oooo", 'O ++? 'Will match a single "O", and 'O +' will match all 'O '.
.	Matches any single character except "\ n. To match any character including '\ n', use a pattern like' [. \ n.
(Pattern)	MatchPatternAnd obtain the matching. The obtained match can be obtained from the generated matches set. It is used in VBScript.SubmatchesSet, which is used in Visual Basic Scripting Edition$0...$9Attribute. To match the parentheses, use '\ (' or '\)'.
(? :Pattern)	MatchPatternBut does not get the matching result, that is, this is a non-get match and is not stored for future use. This is useful when you use the "or" character (|) to combine each part of a pattern. For example, 'industr (? : Y | ies) is a simpler expression than 'industry | industries.
(? =Pattern)	Forward pre-query, in any matchPatternTo start from the string. This is a non-get match, that is, the match does not need to be obtained for future use. For example, 'windows (? = 95 | 98 | nt | 2000) 'can match "Windows" in "Windows 2000", but cannot match "Windows" in "Windows 3.1 ". Pre-query does not consume characters, that is, after a match occurs, the next matching search starts immediately after the last match, instead of starting after the pre-query characters.
(?!Pattern)	Negative pre-query, in any does not match negative lookahead matches the search string at any point where a string not matchingPatternTo start from the string. This is a non-get match, that is, the match does not need to be obtained for future use. For example, 'windows (?! 95 | 98 | nt | 2000) 'can match "Windows" in "Windows 3.1", but cannot match "Windows" in "Windows 2000 ". Pre-query does not consume characters. That is to say, after a match occurs, the next matching search starts immediately after the last match, instead of starting after the pre-query characters.
X|Y	MatchXOrY. For example, 'z | food' can match "Z" or "food ". '(Z | f) Ood' matches "zood" or "food ".
[XYZ]	Character Set combination. Match any character in it. For example, '[ABC]' can match 'A' in "plain '.
[^XYZ]	Negative value character set combination. Match any character not included. For example, '[^ ABC]' can match 'p' in "plain '.
[A-z]	Character range. Matches any character in the specified range. For example, '[A-Z]' can match any lowercase letter in the range of 'A' to 'Z.
[^A-z]	Negative character range. Matches any character that is not within the specified range. For example, '[^ A-Z]' can match any character that is not in the range of 'A' to 'Z.
\ B	Match A Word boundary, that is, the position between a word and a space. For example, 'er \ B 'can match 'er' in "never", but cannot match 'er 'in "verb '.
\ B	Match non-word boundary. 'Er \ B 'can match 'er' in "verb", but cannot match 'er 'in "never '.
\ CX	MatchingXThe specified control character. For example, \ cm matches a control-M or carriage return character.XMust be a A-Z or one of a-Z. Otherwise, C is treated as an original 'C' character.
\ D	Match a numeric character. It is equivalent to [0-9].
\ D	Match a non-numeric character. It is equivalent to [^ 0-9].
\ F	Match a form feed. It is equivalent to \ x0c and \ Cl.
\ N	Match A linefeed. It is equivalent to \ x0a and \ CJ.
\ R	Match a carriage return. It is equivalent to \ x0d and \ cm.
\ S	Matches any blank characters, including spaces, tabs, and page breaks. It is equivalent to [\ f \ n \ r \ t \ v].
\ S	Match any non-blank characters. It is equivalent to [^ \ f \ n \ r \ t \ v].
\ T	Match a tab. It is equivalent to \ x09 and \ CI.
\ V	Match a vertical tab. It is equivalent to \ x0b and \ ck.
\ W	Match any word characters that contain underscores. It is equivalent to '[A-Za-z0-9 _]'.
\ W	Match any non-word characters. It is equivalent to '[^ A-Za-z0-9 _]'.
\ XN	MatchN, WhereNIt is a hexadecimal escape value. The hexadecimal escape value must be determined by the length of two numbers. For example, '\ x41' matches "". '\ X041' is equivalent to '\ x04' & "1 ". The regular expression can use ASCII encoding ..
\Num	MatchNum, WhereNumIs a positive integer. References to the obtained matching. For example, '(.) \ 1' matches two consecutive identical characters.
\N	Identifies an octal escape value or a backward reference. If \NAt leastNObtained subexpressionsNIs backward reference. Otherwise, ifNIs an octal digit (0-7 ),NIt is an octal escape value.
\Nm	Identifies an octal escape value or a backward reference. If \NmAt least is preceded by at leastNmObtain the child expression, thenNmIs backward reference. If \NmAt leastNNIs followed by textM. If none of the preceding conditions are metNAndMAll are Octal numbers (0-7), then \NmMatch the octal escape ValueNm.
\NML	IfNIt is an octal digit (0-3) andMAndLIf the values are Octal numbers (0-7), the octal escape value is matched.NML.
\ UN	MatchN, WhereNIt is a Unicode character represented by four hexadecimal numbers. For example, \ u00a9 matches the copyright symbol (?).