Thread

1. How to allow null as an option when using regexp_matches?

   Shaozhong SHI <shishaozhong@gmail.com> — 2021-12-08T12:07:13Z

   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
   

2. Re: How to allow null as an option when using regexp_matches?

   Karsten Hilbert <karsten.hilbert@gmx.net> — 2021-12-08T12:09:54Z

   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
   
   
   
   

3. Re: How to allow null as an option when using regexp_matches?

   Shaozhong SHI <shishaozhong@gmail.com> — 2021-12-08T17:07:27Z

   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
   >
   >
   >
   

4. Re: How to allow null as an option when using regexp_matches?

   Vincent Veyron <vv.lists@wanadoo.fr> — 2021-12-08T17:40:58Z

   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
   
   
   
   
   

5. Re: How to allow null as an option when using regexp_matches?

   Roxanne Reid-Bennett <rox@tara-lu.com> — 2021-12-08T17:59:19Z

   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