Thread: not like perl..

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???

thanks

hook@lota.us

On Tue, Mar 29, 2011 at 10:18 AM, hook <hook@lota.us> wrote:
> What am I doing wrong???
>


You are confusing regular expressions with SQL "LIKE".  Ie, you are
using "LIKE" and expecting it to match some odd notion of regexp.

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