Thread: Literal '-' in regular expression bracket sets

I'm trying to place a literal '-' in a bracketed character set in a regular
expression for a check constraint. I am currently escaping it with a '\',
however, it still winds up in the table definition as a non-literal dash and
is interpreted as a character range. For instance:

CREATE TABLE retest
(
        hostname VARCHAR(100) CHECK (hostname ~ '^[a-zA-Z0-9\-\.]+$' )
);

works (in the psql utility). Then if I do

INSERT INTO retest(hostname) VALUES ('asdf.com');

psql says

ERROR:  Invalid regular expression: invalid character range in [ ]

If I look at the table definition, the regex reads as '^[a-zA-Z0-9-.]+$'. So
how do I put a literal '-' in the bracket set? Backslashing doesn't seem to
work. Is the '.' being interpreted too? The '.' is supposed to be a literal
'.' as well.

Thanks

On Thu, 5 Sep 2002, Steve wrote:

>
> I'm trying to place a literal '-' in a bracketed character set in a regular
> expression for a check constraint. I am currently escaping it with a '\',
> however, it still winds up in the table definition as a non-literal dash and
> is interpreted as a character range. For instance:
>
> CREATE TABLE retest
> (
>         hostname VARCHAR(100) CHECK (hostname ~ '^[a-zA-Z0-9\-\.]+$' )
> );

I think you might have to move the dash to the beginning of the character
set.  I can't find an easy way to make it happy otherwise. I believe the
'.' doesn't need to be escaped.

Steve <s-psql@rhythm.cx> writes:
> I'm trying to place a literal '-' in a bracketed character set in a regular
> expression for a check constraint.

Per the manual:

To include a literal ] in the list, make it the first character
(following a possible ^). 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 and some
combinations using [ (see next paragraphs), all other special
characters, including \, lose their special significance within a
bracket expression.

            regards, tom lane

On Thu, Sep 05, 2002 at 11:50:55AM -0700, Stephan Szabo wrote:
>
> On Thu, 5 Sep 2002, Steve wrote:
>
> >
> > I'm trying to place a literal '-' in a bracketed character set in a regular
> > expression for a check constraint. I am currently escaping it with a '\',
> > however, it still winds up in the table definition as a non-literal dash and
> > is interpreted as a character range. For instance:
> >
> > CREATE TABLE retest
> > (
> >         hostname VARCHAR(100) CHECK (hostname ~ '^[a-zA-Z0-9\-\.]+$' )
> > );
>
> I think you might have to move the dash to the beginning of the character
> set.  I can't find an easy way to make it happy otherwise. I believe the
> '.' doesn't need to be escaped.

That worked - thanks!