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



Reply via email to