Обсуждение: How to allow null as an option when using regexp_matches?
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
Am Wed, Dec 08, 2021 at 12:07:13PM +0000 schrieb Shaozhong SHI: > 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'); You seem to want to apply coalesce() judiciously. Best, Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Hi, Karsten,
That sounds interesting.
Any good example?
Regards, David
On Wed, 8 Dec 2021 at 12:10, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
Am Wed, Dec 08, 2021 at 12:07:13PM +0000 schrieb Shaozhong SHI:
> 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');
You seem to want to apply coalesce() judiciously.
Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
On Wed, 8 Dec 2021 17:07:27 +0000 Shaozhong SHI <shishaozhong@gmail.com> wrote: > > Any good example? > Plenty of them in the fine documentation : https://www.postgresql.org/search/?q=coalesce -- Bien à vous, Vincent Veyron https://compta.libremen.com Logiciel libre de comptabilité générale en partie double
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|InformationCentre|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|Departmentfor 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