Substring('foobar' from '#'o_b#'%' for '#') NULL Some examples, with #' delimiting the return string: substring('foobar' from '%#'o_b#'%' for '#') oob The text matching the portion of the pattern between these markers is returned. 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 ( '). As with SIMILAR TO, the specified pattern must match the entire data string, or else the function fails and returns null. The substring function with three parameters, substring( string from pattern for escape-character), provides extraction of a substring that matches an SQL regular expression pattern. Some examples: 'abc' SIMILAR TO 'abc' true ) is not a metacharacter for SIMILAR TO.Īs with LIKE, a backslash disables the special meaning of any of these metacharacters or a different escape character can be specified with ESCAPE. Parentheses () can be used to group items into a single logical item.Ī bracket expression specifies a character class, just as in POSIX regular expressions. denotes repetition of the previous item at least m and not more than n times. ? denotes repetition of the previous item zero or one time.

+ denotes repetition of the previous item one or more times. * denotes repetition of the previous item zero or more times. | denotes alternation (either of two alternatives). In addition to these facilities borrowed from LIKE, SIMILAR TO supports these pattern-matching metacharacters borrowed from POSIX regular expressions: Also like LIKE, SIMILAR TO uses _ and % as wildcard characters denoting any single character and any string, respectively (these are comparable to.

Like LIKE, the SIMILAR TO operator 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. SQL regular expressions are a curious cross between LIKE notation and common regular expression notation. It is similar to LIKE, except that it interprets the pattern using the SQL standard's definition of a regular expression. The SIMILAR TO operator returns true or false depending on whether its pattern matches the given string. SIMILAR TO Regular Expressions string SIMILAR TO pattern In some obscure cases it may be necessary to use the underlying operator names instead.ĩ.7.2. The phrases LIKE, ILIKE, NOT LIKE, and NOT ILIKE are generally treated as operators in PostgreSQL syntax for example they can be used in expression operator ANY ( subquery) constructs, although an ESCAPE clause cannot be included there. You may see these operator names in EXPLAIN output and similar places, since the parser actually translates LIKE et al. All of these operators are PostgreSQL-specific. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. This is not in the SQL standard but is a PostgreSQL extension. The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This effectively disables the escape mechanism, which makes it impossible to turn off the special meaning of underscore and percent signs in the pattern. It's also possible to select no escape character by writing ESCAPE ''. See Section 4.1.2.1 for more information. If you have standard_conforming_strings turned off, any backslashes you write in literal string constants will need to be doubled. To match the escape character itself, write two escape characters. The default escape character is the backslash but a different one can be selected by using the ESCAPE clause. To match a literal underscore or percent sign without matching other characters, the respective character in pattern must be preceded by the escape character. Therefore, if it's desired to match a sequence anywhere within a string, the pattern must start and end with a percent sign. LIKE pattern matching always covers the entire string. An underscore ( _) in pattern stands for (matches) any single character a percent sign ( %) matches any sequence of zero or more characters. If pattern does not contain percent signs or underscores, then the pattern only represents the string itself in that case LIKE acts like the equals operator. An equivalent expression is NOT ( string LIKE pattern).) (As expected, the NOT LIKE expression returns false if LIKE returns true, and vice versa. The LIKE expression returns true if the string matches the supplied pattern.

Postgresql regex query