Thread: how to test string against regular expression contained in postgresql database field?

Hi All,

I have a table which contains a field with regular expressions. I want
to test a given string against this field to obtain the resulting
records but I am doing something wrong and cannot find out what.

table
os_regexp os_name
------------- -----------
win.*9x windows
linux linux
etc...

select os_name from table where 'windows9x' ~ os_regexp;
ERROR: invalid regular expression: empty expression or subexpression.


Any help appreciated, Joe.

hi,

Joe Bordes wrote:

> Hi All,
>
> I have a table which contains a field with regular expressions. I want
> to test a given string against this field to obtain the resulting
> records but I am doing something wrong and cannot find out what.
>
> table
> os_regexp os_name
> ------------- -----------
> win.*9x windows
> linux linux
> etc...
>
> select os_name from table where 'windows9x' ~ os_regexp;
> ERROR: invalid regular expression: empty expression or subexpression.

create a function for that.

create or replace function osregexp(varchar,varchar) returns boolean as
' select $1 ~ $2; 'language sql immutable;

SELECT osregexp('windows9x','win.*9x');
  osregexp
----------
  t

C.

CoL <col@mportal.hu> wrote in message news:<c3s060$1k0m$1@news.hub.org>...
>
> create a function for that.
>
> create or replace function osregexp(varchar,varchar) returns boolean as
> ' select $1 ~ $2; 'language sql immutable;
>
> SELECT osregexp('windows9x','win.*9x');
>   osregexp
> ----------
>   t
>
> C.

Hi,

First thanks for answering.

This gives me the same error. I have created the function as you say
and it works when called directly with strings (as above) but not when
called with a table field.

select os_name from table where osregexp('windows9x',os_regexp);
ERROR: invalid regular expression: empty expression or subexpression

I even tried createing the function with STABLE and VOLATILE but I get
the same error

Joe.

On Thu, 25 Mar 2004, Joe Bordes wrote:

> CoL <col@mportal.hu> wrote in message news:<c3s060$1k0m$1@news.hub.org>...
> >
> > create a function for that.
> >
> > create or replace function osregexp(varchar,varchar) returns boolean as
> > ' select $1 ~ $2; 'language sql immutable;
> >
> > SELECT osregexp('windows9x','win.*9x');
> >   osregexp
> > ----------
> >   t
> >
> > C.
>
> Hi,
>
> First thanks for answering.
>
> This gives me the same error. I have created the function as you say
> and it works when called directly with strings (as above) but not when
> called with a table field.
>
> select os_name from table where osregexp('windows9x',os_regexp);
> ERROR: invalid regular expression: empty expression or subexpression

Do you have any strings where the above error might be true?  For example
an empty string or something containing () or other such special values?

joe@tsolucio.com (Joe Bordes) writes:
> I have a table which contains a field with regular expressions. I want
> to test a given string against this field to obtain the resulting
> records but I am doing something wrong and cannot find out what.

> select os_name from table where 'windows9x' ~ os_regexp;
> ERROR: invalid regular expression: empty expression or subexpression.

Looks to me like there's at least one row in the table whose os_regexp
is wrong.  I'm not quite sure *why* it's wrong ... I tried a few
examples to see if I could duplicate that message, and I couldn't find
one.  But you want to look to the regexps themselves, the query is fine.

            regards, tom lane

tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<26388.1080329607@sss.pgh.pa.us>...
> joe@tsolucio.com (Joe Bordes) writes:
> > I have a table which contains a field with regular expressions. I want
> > to test a given string against this field to obtain the resulting
> > records but I am doing something wrong and cannot find out what.
>
> > select os_name from table where 'windows9x' ~ os_regexp;
> > ERROR: invalid regular expression: empty expression or subexpression.
>
> Looks to me like there's at least one row in the table whose os_regexp
> is wrong.  I'm not quite sure *why* it's wrong ... I tried a few
> examples to see if I could duplicate that message, and I couldn't find
> one.  But you want to look to the regexps themselves, the query is fine.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Thank you very much for your answer. You were right, I had an empty
field in one of my rows.

It was my fault. I should have stopped and thought before jumping to
conclusions :-)

Regards, Joe.