From: | Roxanne Reid-Bennett <rox(at)tara-lu(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: How to allow null as an option when using regexp_matches? |
Date: | 2021-12-08 17:59:19 |
Message-ID: | 0101017d9b3320e4-b0918728-a223-434c-afb2-45674c1fcc64-000000@us-west-2.amazonses.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/8/2021 4:07 AM, Shaozhong SHI wrote:
> We can do this:
> select count(*) from regexp_matches('Great London', 'Great London|Information Centre|Department for Transport', 'g');
>
> Is it possible to allow null as an option? something like this
> select count(*) from regexp_matches('Great London', 'null|Great London|Information Centre|Department for Transport', 'g');
>
> Regards,
>
> David
>
Hi David,
I'm assuming that 'Great London' is coming from some column value.
Given that NULL is a state, not a value, regexp really cannot "find" or not "find" it.
you could use COALESCE the source of 'Great London' to a predictable value that you CAN match on.
or you could possibly construct your query something like this:
select CASE WHEN 'Great London' IS NULL THEN 1 ELSE 0 END + (SELECT count(*) from regexp_matches('Great London', 'Great London|Information Centre|Department for Transport', 'g'))
select CASE WHEN NULL IS NULL THEN 1 ELSE 0 END + (SELECT count(*) from regexp_matches(NULL, 'Great London|Information Centre|Department for Transport', 'g'))
Interestingly to me, the following returns 2 - possibly because an empty string matches anything?
select count(*) from regexp_matches('Great London', 'Great London||Information Centre|Department for Transport', 'g');
Roxanne
From | Date | Subject | |
---|---|---|---|
Next Message | David Gauthier | 2021-12-08 19:44:47 | performance expectations for table(s) with 2B recs |
Previous Message | Paul van der Linden | 2021-12-08 17:58:22 | Re: CTE Materialization |