Question on the use of bracket expressions in Postgres
Good morning,
I am new to Postgres and I have one question. I am trying to get the
following select command to work.
select * from catalog where file_name like 'abc%def%.200[2-5]%';
The following select keeps returning "(0 rows)".
I know that there are many rows in my database that should be returned
because I first did a
select * from catalog where file_name like 'abc%def%.200%';
I've tried escaping the [ and ] metacharacters and even the - but I got the
same results: (0 rows).
What is the correct syntax to pull out a range of data from my database?
Thanks.
Jimmy
On Dec 15, 2005, at 0:29 , Jimmy Rowe wrote:
select * from catalog where file_name like 'abc%def%.200[2-5]%';
The following select keeps returning "(0 rows)".
LIKE doesn't consider [2-5] to be a range, but rather the literal
characters '[2-5]'. If you're looking for regex, take a look at the
POSIX regex operator ~
http://www.postgresql.org/docs/current/interactive/functions-
matching.html#FUNCTIONS-POSIX-REGEXP
See if something like file_name ~ 'abc.*def.*\.200[2-5]'
select '2003' ~ '200[2-5]' as "yup", '2006' ~ '200[2-5]' as "nope";
yup | nope
-----+------
t | f
(1 row)
Michael Glaesemann
grzm myrealbox com
In article <F96F2B16-2A2A-4A28-97E1-1B00C9902808@myrealbox.com>,
Michael Glaesemann <grzm@myrealbox.com> writes:
On Dec 15, 2005, at 0:29 , Jimmy Rowe wrote:
select * from catalog where file_name like 'abc%def%.200[2-5]%';
The following select keeps returning "(0 rows)".
LIKE doesn't consider [2-5] to be a range, but rather the literal
characters '[2-5]'. If you're looking for regex, take a look at the
POSIX regex operator ~
http://www.postgresql.org/docs/current/interactive/functions-
matching.html#FUNCTIONS-POSIX-REGEXP
See if something like file_name ~ 'abc.*def.*\.200[2-5]'
That's not quite the same because LIKE matching is anchored.
Try something like file_name ~ '^abc.*def.*\.200[2-5]$'