Re: not like perl..

On Mar 29, 2011, at 10:18 AM, hook wrote:

> I have a simple table with a varchar(32) field that I am trying to extract data using regular expressions.
>
> select * from spam where inetaddr like '100.%'
> row  |   inetaddr   |           tdate          ------+--------------+---------------------------
> 3245 | 100.81.98.51 | 03/08/2011 07:21:19.29209    -----works fine
>
>
> select * from spam where inetaddr like E'\d\d\d.%'
> row | inetaddr | tdate
> -----+----------+-------
> (0 rows)                                                                      --- zip ???????????
>
> slect * from spam where inetaddr like E'\d.%'
> row  |            inetaddr            |           tdate
-------+--------------------------------+----------------------------
> 49424 | d.russell_ul@jdmarketing.co.uk | 03/27/2011 15:46:41.110566    ??????????? though \d was a digit match????
>
>
> select * from spam where inetaddr like E'\\d.%'
> row  |            inetaddr            |           tdate
-------+--------------------------------+----------------------------
> 49424 | d.russell_ul@jdmarketing.co.uk | 03/27/2011 15:46:41.110566   ???????????
>
>
> What am I doing wrong???

You are not using the regular expression operator.

test=# create table test(a text);
CREATE TABLE
test=# insert into test(a) values ('100.81.98.51');
INSERT 0 1
test=# select * from test where a ~ $$^\d+\.$$;
      a
--------------
 100.81.98.51
(1 row)

This is just like perl.

Cheers,
M