Thread: psql regular expression matching POSIX

Could someone please provide me with the syntax for a REGEXP that will
match a column in a table?

ie, this works using "similar to":
   select prod_id, prod_desc, vend_id from products where\
       vend_id similar to '\[D|F]%'


In the docs:
   pgsql/doc/html/functions-matching.html  para 9.7.3

I'm suppose to be able to use POSIX regular expressions.

Say I want to match all the vendor IDs with "LL" located anywere
inside the vend_id.

    select vend_id from products where <Now What???>

All of the examples in this section discuss "select substring"

All the variations I've tried of the following either are
syntax errors or 'type "vend_id" does not exist'.
   SELECT vend_id from products  where  substring (vend_id '.*LL.*');

Thanks

I finally reread the page and figured it out.

select vend_id from products where vend_id ~ '.*LL.*'
                                           ^^^^

Somehow I couldn't grok the idea of an operator vs a keyword.

John Zwiebel wrote:
> Could someone please provide me with the syntax for a REGEXP that will
> match a column in a table?
>
> ie, this works using "similar to":
>   select prod_id, prod_desc, vend_id from products where\
>       vend_id similar to '\[D|F]%'
>
>
> In the docs:
>   pgsql/doc/html/functions-matching.html  para 9.7.3
>
> I'm suppose to be able to use POSIX regular expressions.
>
> Say I want to match all the vendor IDs with "LL" located anywere
> inside the vend_id.
>
>    select vend_id from products where <Now What???>
>
> All of the examples in this section discuss "select substring"
>
> All the variations I've tried of the following either are
> syntax errors or 'type "vend_id" does not exist'.
>   SELECT vend_id from products  where  substring (vend_id '.*LL.*');
>
> Thanks