On Nov 16, 2014, at 3:52 PM, Seamus Abshere sea...@abshere.net wrote:
hi,
I want to check if two similar-looking addresses have the same numbered
street, like 20th versus 21st.
2033 21st Ave S
2033 20th Ave S (they're different)
I get an error:
# select regexp_matches('2033 21st Ave S', '\m(\d+(?:st|th))\M') =
regexp_matches('2033 20th Ave S', '\m(\d+(?:st|th))\M');
ERROR: functions and operators can take at most one set argument
I've tried `()[1] == ()[1]`, etc. but the only thing that works is
making it into 2 subqueries:
# select (select * from regexp_matches('2033 21st Ave S',
'\m(\d+(?:st|th))\M')) = (select * from regexp_matches('2033 20th
Ave S', '\m(\d+(?:st|th))\M'));
?column?
--
f
(1 row)
Is there a more elegant way to compare the results of
`regexp_matches()`?
Probably not - that's the documented way to force regexp_matches() to return a
single row, whether it matches or not.
But I think you want to use substring(), rather than regexp_matches(), eg:
select substring('2033 21st Ave S' from '\m(\d+(?:st|th))\M') =
substring('2033 20th Ave S' from '\m(\d+(?:st|th))\M');
substring() will return the first capturing group, if there is one, or the
whole match otherwise.
Given that the whole pattern you're using here, other than some zero-width
assertions, is a capturing group the result is the same either way. You could
rewrite it without capturing and get the same result:
select substring('2033 21st Ave S' from '\m\d+(?:st|th)\M') =
substring('2033 20th Ave S' from '\m\d+(?:st|th)\M');
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
