Thomas Lockhart wrote:
> Right. I'm not certain about the regex syntax defined by SQL99; I used
> the syntax that we already have enabled and it looks like we have a
> couple of other variants available if we need them. If someone wants to
> research the *actual* syntax specified by SQL99 that would be good...
As usual: ( ) + * [ ] |
Instead of dot . there is underscore _
There is % to mean .* just like LIKE
There is no ? or ^ or $
Regular expressions match the whole string, as if there were an
implicit ^ before and $ after the pattern. You have to add % if
you want to match anywhere in a string.
As far as I can tell, there is no default escape character like \
but you can specify one.
8.6 Similar predicate
Function
Specify a character string similarity by means of a regular expression.
Format
<similar predicate> ::=
<character match value> [ NOT ] SIMILAR TO <similar pattern>
[ ESCAPE <escape character> ]
<similar pattern> ::= <character value expression>
<regular expression> ::=
<regular term>
| <regular expression> <vertical bar> <regular term>
<regular term> ::=
<regular factor>
| <regular term> <regular factor>
<regular factor> ::=
<regular primary>
| <regular primary> <asterisk>
| <regular primary> <plus sign>
<regular primary> ::=
<character specifier>
| <percent>
| <regular character set>
| <left paren> <regular expression> <right paren>
<character specifier> ::=
<non-escaped character>
| <escaped character>
<non-escaped character> ::= !! See the Syntax Rules
<escaped character> ::= !! See the Syntax Rules
<regular character set> ::=
<underscore>
| <left bracket> <character enumeration>... <right bracket>
| <left bracket> <circumflex>
<character enumeration>... <right bracket>
| <left bracket> <colon> <regular character set identifier>
<colon> <right bracket>
<character enumeration> ::=
<character specifier>
| <character specifier> <minus sign> <character specifier>
<regular character set identifier> ::= <identifier>
*stuff omitted*
3) The value of the <identifier> that is a <regular character set
identifier> shall be either ALPHA, UPPER, LOWER, DIGIT, or ALNUM.
*collating stuff omitted*
5) A <non-escaped character> is any single character from the
character set of the <similar pattern> that is not a <left bracket>,
<right bracket>, <left paren>, <right paren>, <vertical bar>,
<circumflex>, <minus sign>, <plus sign>, <asterisk>, <underscore>,
<percent>, or the character specified by the result of the <character
value expression> of <escape character>. A <character specifier> that
is a <non-escaped character> represents itself.
6) An <escaped character> is a sequence of two characters: the
character specified by the result of the <character value expression>
of <escape character>, followed by a second character that is a <left
bracket>, <right bracket>, <left paren>, <right paren>, <vertical
bar>, <circumflex>, <minus sign>, <plus sign>, <asterisk>,
<underscore>, <percent>, or the character specified by the result of
the <character value expression> of <escape character>. A <character
specifier> that is an <escaped character> represents its second
character.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
