Reference http://www.php100.com/manual/PostgreSQL8/functions-matching.html
like
string LIKE pattern [ ESCAPE escape-character ]
string NOT LIKE pattern [ ESCAPE escape-character ]
Eachpatterndefines a collection of strings. If thestringis contained in a string collection represented bypattern, thelike expression returns True. (as we imagined, if like returnstrue, then the not-like expression returns false, and vice versa.) An equivalent expression is not(stringlikepattern).)
Ifpatterndoes not contain a percent sign or an underscore, then the pattern represents itself only,and the like behaves as an equals operator. The underscore (_) in thepatternrepresents (matches) any single character, and a percent symbol (%) matches any string of zero or more characters long.
Here are some examples:
‘abc‘ LIKE ‘abc‘ true
‘abc‘ LIKE ‘a%‘ true
‘abc‘ LIKE ‘_b_‘ true
‘abc‘ LIKE ‘c‘ false
like pattern matching always overwrites the entire string. To match a sequence anywhere inside a string, the pattern must begin and end with a percent sign. The like operator can also match the specified string, but unlike the ~, a similar string is not found if it appears in the middle of the text it, the corresponding row is not returned. and ~ in the text to match, if the matched string appears in the text, ~ will find it, the corresponding row is returned.
Table Regular expression matching operator (Note: ~ equals like)
operator |
Description |
Example |
~ |
Match regular expressions, case-related |
' Thomas ' ~ '. *thomas.* ' |
~* |
Match regular expression, case-insensitive |
' Thomas ' ~* '. *thomas.* ' |
!~ |
Mismatched regular expressions, case-related |
' Thomas '!~ '. *thomas.* ' |
!~* |
Mismatched regular expressions, case-insensitive |
' Thomas '!~* '. *vadim.* ' |
Regular Expression quantifier
quantifier |
Match |
* |
A sequence that matches 0 or more atoms |
+ |
A sequence that matches 1 or more atoms |
? |
A sequence that matches 0 or 1 atoms |
{m} |
A sequence that exactly matchesmatoms |
{m,} |
A sequence that matchesmor more atoms |
{m,n} |
A sequence of atoms that matchesmtoN(containing two ends);mcannot be larger thann |
*? |
*non-greedy mode |
+? |
+non-greedy mode |
?? |
?The non-greedy mode |
{m}? |
Non-greedy mode for{m} |
{m,}? |
Non-greedy mode for{m,} |
{m,n}? |
Non-greedy mode for{m,n} |
{...The form of the}is called a range . NumbersmandNin a range are unsigned decimal integers, allowing values from 0 to 255 (closed interval).
Regular expression constraints
Constraints |
Description |
^ |
Match the beginning of a string |
$ |
Match the end of a string |
(? =re) |
forward matching of any substring starting point matching there(only in IS) |
(?!Re) |
Negative lookahead matches any starting point of the substring that does not match there. (Only in IS) |
PostgreSQL Regular Expression Query