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(dot)russell_ul(at)jdmarketing(dot)co(dot)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(dot)russell_ul(at)jdmarketing(dot)co(dot)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