Postgresql - Pattern Matching
There are three separate approaches to pattern matching provided byPostgreSQL: the traditionalSQLLIKEoperator,the more recentSIMILAR TOoperator (added in SQL:1999),andPOSIX-style regular expressions. Aside from the basic"does this string match this pattern?"operators,functions are available to extract or replace matching substrings and to split a string at matching locations.
9.7.1.LIKEstringLIKEpattern[ESCAPEescape-character]stringNOT LIKEpattern[ESCAPEescape-character] TheLIKEexpression returns true if thestringmatches the suppliedpattern. (As expected,theNOT LIKEexpression returns false ifLIKEreturns true,and vice versa. An equivalent expression isNOT (stringLIKEpattern).) Ifpatterndoes not contain percent signs or underscores,then the pattern only represents the string itself; in that caseLIKEacts like the equals operator. An underscore (_) inpatternstands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters. Some examples: 'abc' LIKE 'abc' true 'abc' LIKE 'a%' true 'abc' LIKE '_b_' true 'abc' LIKE 'c' false LIKEpattern matching always covers the entire string. Therefore,to match a sequence anywhere within a string,the pattern must start and end with a percent sign. To match a literal underscore or percent sign without matching other characters,the respective character inpatternmust be preceded by the escape character. The default escape character is the backslash but a different one can be selected by using theESCAPEclause. To match the escape character itself,write two escape characters. Note that the backslash already has a special meaning in string literals,so to write a pattern constant that contains a backslash you must write two backslashes in an SQL statement (assuming escape string syntax is used,seeSection 4.1.2.1). Thus,writing a pattern that actually matches a literal backslash means writing four backslashes in the statement. You can avoid this by selecting a different escape character withESCAPE; then a backslash is not special toLIKEanymore. (But backslash is still special to the string literal parser,so you still need two of them to match a backslash.) It's also possible to select no escape character by writingESCAPE ''. This effectively disables the escape mechanism,which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern. The key wordILIKEcan be used instead ofLIKEto make the match case-insensitive according to the active locale. This is not in theSQLstandard but is aPostgreSQLextension. The operator~~is equivalent toLIKE,and~~*corresponds toILIKE. There are also!~~and!~~*operators that representNOT LIKEandNOT ILIKE,respectively. All of these operators arePostgreSQL-specific. 9.7.2.SIMILAR TORegular ExpressionsstringSIMILAR TOpattern[ESCAPEescape-character]stringNOT SIMILAR TOpattern[ESCAPEescape-character] TheSIMILAR TOoperator returns true or false depending on whether its pattern matches the given string. It is similar toLIKE,except that it interprets the pattern using the SQL standard's definition of a regular expression. SQL regular expressions are a curious cross betweenLIKEnotation and common regular expression notation. LikeLIKE,theSIMILAR TOoperator succeeds only if its pattern matches the entire string; this is unlike common regular expression behavior where the pattern can match any part of the string. Also likeLIKE,SIMILAR TOuses_and%as wildcard characters denoting any single character and any string,respectively (these are comparable to.and.*in POSIX regular expressions). In addition to these facilities borrowed fromLIKE,SIMILAR TOsupports these pattern-matching metacharacters borrowed from POSIX regular expressions:
Notice that the period (.) is not a metacharacter forSIMILAR TO. As withLIKE,a backslash disables the special meaning of any of these metacharacters; or a different escape character can be specified withESCAPE. Some examples: 'abc' SIMILAR TO 'abc' true 'abc' SIMILAR TO 'a' false 'abc' SIMILAR TO '%(b|d)%' true 'abc' SIMILAR TO '(b|c)%' false Thesubstringfunction with three parameters,substring(stringfrompatternforescape-character),provides extraction of a substring that matches an SQL regular expression pattern. As withSIMILAR TO,the specified pattern must match the entire data string,or else the function fails and returns null. To indicate the part of the pattern that should be returned on success,the pattern must contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these markers is returned. Some examples,with#"delimiting the return string: substring('foobar' from '%#"o_b#"%' for '#') oob substring('foobar' from '#"o_b#"%' for '#') NULL
9.7.3.POSIXRegular ExpressionsTable 9-11lists the available operators for pattern matching using POSIX regular expressions. Table 9-11. Regular Expression Match Operators
POSIXregular expressions provide a more powerful means for pattern matching than theLIKEandSIMILAR TOoperators. Many Unix tools such asegrep,sed,orawkuse a pattern matching language that is similar to the one described here. A regular expression is a character sequence that is an abbreviated definition of a set of strings (aregular set). A string is said to match a regular expression if it is a member of the regular set described by the regular expression. As withLIKE,pattern characters match string characters exactly unless they are special characters in the regular expression language — but regular expressions use different special characters thanLIKEdoes. UnlikeLIKEpatterns,a regular expression is allowed to match anywhere within a string,unless the regular expression is explicitly anchored to the beginning or end of the string. Some examples: 'abc' ~ 'abc' true 'abc' ~ '^a' true 'abc' ~ '(b|d)' true 'abc' ~ '^(b|c)' false ThePOSIXpattern language is described in much greater detail below. Thesubstringfunction with two parameters,substring(stringfrompattern),provides extraction of a substring that matches a POSIX regular expression pattern. It returns null if there is no match,otherwise the portion of the text that matched the pattern. But if the pattern contains any parentheses,the portion of the text that matched the first parenthesized subexpression (the one whose left parenthesis comes first) is returned. You can put parentheses around the whole expression if you want to use parentheses within it without triggering this exception. If you need parentheses in the pattern before the subexpression you want to extract,see the non-capturing parentheses described below. Some examples: substring('foobar' from 'o.b') oob substring('foobar' from 'o(.)b') o Theregexp_replacefunction provides substitution of new text for substrings that match POSIX regular expression patterns. It has the syntaxregexp_replace(source,pattern,replacement[,flags]). Thesourcestring is returned unchanged if there is no match to thepattern. If there is a match,thesourcestring is returned with thereplacementstring substituted for the matching substring. Thereplacementstring can containn,wherenis1through9,to indicate that the source substring matching then'th parenthesized subexpression of the pattern should be inserted,and it can contain&;to indicate that the substring matching the entire pattern should be inserted. Writeif you need to put a literal backslash in the replacement text. (As always,remember to double backslashes written in literal constant strings,assuming escape string syntax is used.) Theflagsparameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flagispecifies case-insensitive matching,while flaggspecifies replacement of each matching substring rather than only the first one. Other supported flags are described inTable 9-19. Some examples: regexp_replace('foobarbaz','b..','X') fooXbaz regexp_replace('foobarbaz','X','g') fooXX regexp_replace('foobarbaz','b(..)',E'X1Y','g') fooXarYXazY Theregexp_matchesfunction returns a text array of all of the captured substrings resulting from matching a POSIX regular expression pattern. It has the syntaxregexp_matches(string,pattern[,flags]). The function can return no rows,one row,or multiple rows (see thegflag below). If thepatterndoes not match,the function returns no rows. If the pattern contains no parenthesized subexpressions,then each row returned is a single-element text array containing the substring matching the whole pattern. If the pattern contains parenthesized subexpressions,the function returns a text array whosen'th element is the substring matching then'th parenthesized subexpression of the pattern (not counting"non-capturing"parentheses; see below for details). Theflagsparameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flaggcauses the function to find each match in the string,not only the first one,and return a row for each such match. Other supported flags are described inTable 9-19. Some examples: SELECT regexp_matches('foobarbequebaz','(bar)(beque)'); regexp_matches ---------------- {bar,beque} (1 row) SELECT regexp_matches('foobarbequebazilbarfbonk','(b[^b]+)(b[^b]+)','g'); regexp_matches ---------------- {bar,beque} {bazil,barf} (2 rows) SELECT regexp_matches('foobarbequebaz','barbeque'); regexp_matches ---------------- {barbeque} (1 row) It is possible to forceregexp_matches()to always return one row by using a sub-select; this is particularly useful in aSELECTtarget list when you want all rows returned,even non-matching ones: SELECT col1,(SELECT regexp_matches(col2,'(bar)(beque)')) FROM tab; Theregexp_split_to_tablefunction splits a string using a POSIX regular expression pattern as a delimiter. It has the syntaxregexp_split_to_table(string,flags]). If there is no match to thepattern,the function returns thestring. If there is at least one match,for each match it returns the text from the end of the last match (or the beginning of the string) to the beginning of the match. When there are no more matches,it returns the text from the end of the last match to the end of the string. Theflagsparameter is an optional text string containing zero or more single-letter flags that change the function's behavior.regexp_split_to_tablesupports the flags described inTable 9-19. Theregexp_split_to_arrayfunction behaves the same asregexp_split_to_table,except thatregexp_split_to_arrayreturns its result as an array oftext. It has the syntaxregexp_split_to_array(string,flags]). The parameters are the same as forregexp_split_to_table. Some examples: SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog',E's+') AS foo; foo -------- the quick brown fox jumped over the lazy dog (9 rows) SELECT regexp_split_to_array('the quick brown fox jumped over the lazy dog',E's+'); regexp_split_to_array ------------------------------------------------ {the,quick,brown,fox,jumped,over,the,lazy,dog} (1 row) SELECT foo FROM regexp_split_to_table('the quick brown fox',E's*') AS foo; foo ----- t h e q u i c k b r o w n f o x (16 rows) As the last example demonstrates,the regexp split functions ignore zero-length matches that occur at the start or end of the string or immediately after a previous match. This is contrary to the strict definition of regexp matching that is implemented byregexp_matches,but is usually the most convenient behavior in practice. Other software systems such as Perl use similar definitions. 9.7.3.1. Regular Expression DetailsPostgreSQL's regular expressions are implemented using a software package written by Henry Spencer. Much of the description of regular expressions below is copied verbatim from his manual. Regular expressions (REs),as defined inPOSIX1003.2,come in two forms:extendedREs orEREs (roughly those ofegrep),andbasicREs orBREs (roughly those ofed).PostgreSQLsupports both forms,and also implements some extensions that are not in the POSIX standard,but have become widely used due to their availability in programming languages such as Perl and Tcl.REs using these non-POSIX extensions are calledadvancedREs orAREs in this documentation. AREs are almost an exact superset of EREs,but BREs have several notational incompatibilities (as well as being much more limited). We first describe the ARE and ERE forms,noting features that apply only to AREs,and then describe how BREs differ.
A regular expression is defined as one or morebranches,separated by|. It matches anything that matches one of the branches. A branch is zero or morequantified atomsorconstraints,concatenated. It matches a match for the first,followed by a match for the second,etc; an empty branch matches the empty string. A quantified atom is anatompossibly followed by a singlequantifier. Without a quantifier,it matches a match for the atom. With a quantifier,it can match some number of matches of the atom. Anatomcan be any of the possibilities shown inTable 9-12. The possible quantifiers and their meanings are shown inTable 9-13. Aconstraintmatches an empty string,but matches only when specific conditions are met. A constraint can be used where an atom could be used,except it cannot be followed by a quantifier. The simple constraints are shown inTable 9-14; some more constraints are described later. Table 9-12. Regular Expression Atoms
An RE cannot end with.
Table 9-13. Regular Expression Quantifiers
The forms using{...}are known asbounds. The numbersmandnwithin a bound are unsigned decimal integers with permissible values from 0 to 255 inclusive. Non-greedyquantifiers (available in AREs only) match the same possibilities as their corresponding normal (greedy) counterparts,but prefer the smallest number rather than the largest number of matches. SeeSection 9.7.3.5for more detail.
Table 9-14. Regular Expression Constraints
Lookahead constraints cannot containback references(seeSection 9.7.3.3),and all parentheses within them are considered non-capturing. 9.7.3.2. Bracket ExpressionsAbracket expressionis a list of characters enclosed in[]. It normally matches any single character from the list (but see below). If the list begins with^,it matches any single characternotfrom the rest of the list. If two characters in the list are separated by-,this is shorthand for the full range of characters between those two (inclusive) in the collating sequence,[0-9]inASCIImatches any decimal digit. It is illegal for two ranges to share an endpoint,a-c-e. Ranges are very collating-sequence-dependent,so portable programs should avoid relying on them. To include a literal]in the list,make it the first character (after^,if that is used). To include a literal-,make it the first or last character,or the second endpoint of a range. To use a literal-as the first endpoint of a range,enclose it in[.and.]to make it a collating element (see below). With the exception of these characters,some combinations using[(see next paragraphs),and escapes (AREs only),all other special characters lose their special significance within a bracket expression. In particular,is not special when following ERE or BRE rules,though it is special (as introducing an escape) in AREs. Within a bracket expression,a collating element (a character,a multiple-character sequence that collates as if it were a single character,or a collating-sequence name for either) enclosed in[.and.]stands for the sequence of characters of that collating element. The sequence is treated as a single element of the bracket expression's list. This allows a bracket expression containing a multiple-character collating element to match more than one character,if the collating sequence includes achcollating element,then the RE[[.ch.]]*cmatches the first five characters ofchchcc.
Within a bracket expression,a collating element enclosed in[=and=]is anequivalence class,standing for the sequences of characters of all collating elements equivalent to that one,including itself. (If there are no other equivalent collating elements,the treatment is as if the enclosing delimiters were[.and.].) For example,ifoand^are the members of an equivalence class,then[[=o=]],[[=^=]],and[o^]are all synonymous. An equivalence class cannot be an endpoint of a range. Within a bracket expression,the name of a character class enclosed in[:and:]stands for the list of all characters belonging to that class. Standard character class names are:alnum,alpha,blank,cntrl,digit,graph,lower,print,punct,space,upper,xdigit. These stand for the character classes defined inctype. A locale can provide others. A character class cannot be used as an endpoint of a range. There are two special cases of bracket expressions: the bracket expressions[[:<:]]and[[:>:]]are constraints,matching empty strings at the beginning and end of a word respectively. A word is defined as a sequence of word characters that is neither preceded nor followed by word characters. A word character is analnumcharacter (as defined byctype) or an underscore. This is an extension,compatible with but not specified byPOSIX1003.2,and should be used with caution in software intended to be portable to other systems. The constraint escapes described below are usually preferable; they are no more standard,but are easier to type. 9.7.3.3. Regular Expression EscapesEscapesare special sequences beginning withfollowed by an alphanumeric character. Escapes come in several varieties: character entry,class shorthands,constraint escapes,and back references. Afollowed by an alphanumeric character but not constituting a valid escape is illegal in AREs. In EREs,there are no escapes: outside a bracket expression,afollowed by an alphanumeric character merely stands for that character as an ordinary character,and inside a bracket expression,is an ordinary character. (The latter is the one actual incompatibility between EREs and AREs.) Character-entry escapesexist to make it easier to specify non-printing and other inconvenient characters in REs. They are shown inTable 9-15. Class-shorthand escapesprovide shorthands for certain commonly-used character classes. They are shown inTable 9-16. Aconstraint escapeis a constraint,matching the empty string if specific conditions are met,written as an escape. They are shown inTable 9-17. Aback reference(n) matches the same string matched by the previous parenthesized subexpression specified by the numbern(seeTable 9-18). For example,([bc])1matchesbborccbut notbcorcb. The subexpression must entirely precede the back reference in the RE. Subexpressions are numbered in the order of their leading parentheses. Non-capturing parentheses do not define subexpressions.
Table 9-15. Regular Expression Character-Entry Escapes
Hexadecimal digits are0-9,a-f,andA-F. Octal digits are0-7. The character-entry escapes are always taken as ordinary characters. For example,135is]in ASCII,but135does not terminate a bracket expression. Table 9-16. Regular Expression Class-Shorthand Escapes
Within bracket expressions,d,s,andwlose their outer brackets,andD,S,andWare illegal. (So,for example,[a-cd]is equivalent to[a-c[:digit:]]. Also,[a-cD],which is equivalent to[a-c^[:digit:]],is illegal.) Table 9-17. Regular Expression Constraint Escapes
A word is defined as in the specification of[[:<:]]and[[:>:]]above. Constraint escapes are illegal within bracket expressions. Table 9-18. Regular Expression Back References
9.7.3.4. Regular Expression MetasyntaxIn addition to the main syntax described above,there are some special forms and miscellaneous syntactic facilities available. An RE can begin with one of two specialdirectorprefixes. If an RE begins with***:,the rest of the RE is taken as an ARE. (This normally has no effect inPostgreSQL,since REs are assumed to be AREs; but it does have an effect if ERE or BRE mode had been specified by theflagsparameter to a regex function.) If an RE begins with***=,the rest of the RE is taken to be a literal string,with all characters considered ordinary characters. An ARE can begin withembedded options: a sequence(?xyz)(wherexyzis one or more alphabetic characters) specifies options affecting the rest of the RE. These options override any previously determined options — in particular,they can override the case-sensitivity behavior implied by a regex operator,or theflagsparameter to a regex function. The available option letters are shown inTable 9-19. Note that these same option letters are used in theflagsparameters of regex functions. Table 9-19. ARE Embedded-Option Letters
Embedded options take effect at the)terminating the sequence. They can appear only at the start of an ARE (after the***:director if any). In addition to the usual (tight) RE syntax,in which all characters are significant,there is anexpandedsyntax,available by specifying the embeddedxoption. In the expanded syntax,white-space characters in the RE are ignored,as are all characters between a#and the following newline (or the end of the RE). This permits paragraphing and commenting a complex RE. There are three exceptions to that basic rule:
For this purpose,white-space characters are blank,tab,newline,and any character that belongs to thespacecharacter class. Finally,in an ARE,outside bracket expressions,the sequence(?#ttt)(wheretttis any text not containing a)) is a comment,completely ignored. Again,this is not allowed between the characters of multi-character symbols,like(?:. Such comments are more a historical artifact than a useful facility,and their use is deprecated; use the expanded syntax instead. Noneof these metasyntax extensions is available if an initial***=director has specified that the user's input be treated as a literal string rather than as an RE. 9.7.3.5. Regular Expression Matching RulesIn the event that an RE could match more than one substring of a given string,the RE matches the one starting earliest in the string. If the RE could match more than one substring starting at that point,either the longest possible match or the shortest possible match will be taken,depending on whether the RE isgreedyornon-greedy. Whether an RE is greedy or not is determined by the following rules:
The above rules associate greediness attributes not only with individual quantified atoms,but with branches and entire REs that contain quantified atoms. What that means is that the matching is done in such a way that the branch,or whole RE,matches the longest or shortest possible substringas a whole. Once the length of the entire match is determined,the part of it that matches any particular subexpression is determined on the basis of the greediness attribute of that subexpression,with subexpressions starting earlier in the RE taking priority over ones starting later. An example of what this means: SELECT SUBSTRING('XY1234Z','Y*([0-9]{1,3})'); Result: 123SELECT SUBSTRING('XY1234Z','Y*?([0-9]{1,3})'); Result: 1 In the first case,the RE as a whole is greedy becauseY*is greedy. It can match beginning at theY,and it matches the longest possible string starting there,i.e.,Y123. The output is the parenthesized part of that,or123. In the second case,the RE as a whole is non-greedy becauseY*?is non-greedy. It can match beginning at theY,and it matches the shortest possible string starting there,Y1. The subexpression[0-9]{1,3}is greedy but it cannot change the decision as to the overall match length; so it is forced to match just1. In short,when an RE contains both greedy and non-greedy subexpressions,the total match length is either as long as possible or as short as possible,according to the attribute assigned to the whole RE. The attributes assigned to the subexpressions only affect how much of that match they are allowed to"eat"relative to each other. The quantifiers{1,1}and{1,1}?can be used to force greediness or non-greediness,respectively,on a subexpression or a whole RE. Match lengths are measured in characters,not collating elements. An empty string is considered longer than no match at all. For example:bb*matches the three middle characters ofabbbc;(week|wee)(night|knights)matches all ten characters ofweeknights; when(.*).*is matched againstabcthe parenthesized subexpression matches all three characters; and when(a*)*is matched againstbcboth the whole RE and the parenthesized subexpression match an empty string. If case-independent matching is specified,the effect is much as if all case distinctions had vanished from the alphabet. When an alphabetic that exists in multiple cases appears as an ordinary character outside a bracket expression,it is effectively transformed into a bracket expression containing both cases,xbecomes[xX]. When it appears inside a bracket expression,all case counterparts of it are added to the bracket expression,[x]becomes[xX]and[^x]becomes[^xX]. If newline-sensitive matching is specified,.and bracket expressions using^will never match the newline character (so that matches will never cross newlines unless the RE explicitly arranges it) and^and$will match the empty string after and before a newline respectively,in addition to matching at beginning and end of string respectively. But the ARE escapesAandZcontinue to match beginning or end of stringonly. If partial newline-sensitive matching is specified,this affects.and bracket expressions as with newline-sensitive matching,but not^and$. If inverse partial newline-sensitive matching is specified,this affects^and$as with newline-sensitive matching,but not.and bracket expressions. This isn't very useful but is provided for symmetry. 9.7.3.6. Limits and CompatibilityNo particular limit is imposed on the length of REs in this implementation. However,programs intended to be highly portable should not employ REs longer than 256 bytes,as a POSIX-compliant implementation can refuse to accept such REs. The only feature of AREs that is actually incompatible with POSIX EREs is thatdoes not lose its special significance inside bracket expressions. All other ARE features use syntax which is illegal or has undefined or unspecified effects in POSIX EREs; the***syntax of directors likewise is outside the POSIX syntax for both BREs and EREs. Many of the ARE extensions are borrowed from Perl,but some have been changed to clean them up,and a few Perl extensions are not present. Incompatibilities of note includeb,B,the lack of special treatment for a trailing newline,the addition of complemented bracket expressions to the things affected by newline-sensitive matching,the restrictions on parentheses and back references in lookahead constraints,and the longest/shortest-match (rather than first-match) matching semantics. Two significant incompatibilities exist between AREs and the ERE syntax recognized by pre-7.4 releases ofPostgreSQL:
9.7.3.7. Basic Regular ExpressionsBREs differ from EREs in several respects. In BREs,|,+,and?are ordinary characters and there is no equivalent for their functionality. The delimiters for bounds are{and},with{and}by themselves ordinary characters. The parentheses for nested subexpressions are(and),with(and)by themselves ordinary characters.^is an ordinary character except at the beginning of the RE or the beginning of a parenthesized subexpression,$is an ordinary character except at the end of the RE or the end of a parenthesized subexpression,and*is an ordinary character if it appears at the beginning of the RE or the beginning of a parenthesized subexpression (after a possible leading^). Finally,single-digit back references are available,and&;and&;are synonyms for[[:<:]]and[[:>:]]respectively; no other escapes are available in BREs. 以下部分来自:http://www.cnblogs.com/stephen-liu74/archive/2012/05/04/2294643.html PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |