regex in postgres 8.4

Question

I am trying to write this query:

SELECT DISTINCT createdcfgid FROM ab WHERE (createdcfgid ~ ‘^[0-9]+$’)

This results in

 syntax error at or near "[" LINE 3: WHERE (createdcfgid ~ ‘^[0-9]+$’)

Anyone there who can give me a clue about what I am doing wrong?

Thanks in advance

user533832 · Accepted Answer · 2012-11-29 10:17:59Z

1

It does just look like you are using the wrong quotes, try ' rather than ‘:

PostgreSQL 8.4 Schema Setup:

create table ab(createdcfgid text);
insert into ab(createdcfgid) values ('111');

Query:

SELECT DISTINCT createdcfgid FROM ab WHERE (createdcfgid ~ '^[0-9]+$')

Results:

| CREATEDCFGID |
----------------
|          111 |

answered Nov 29, 2012 at 10:17

user533832

Sign up to request clarification or add additional context in comments.

1 Answer 1