Re: Comparing results of regexp_matches

On Nov 16, 2014, at 3:52 PM, Seamus Abshere <seamus(at)abshere(dot)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