等待 PostgreSQL 15 – 添加各种新的 regexp_xxx SQL 函数

添加各种新的 regexp_xxx SQL 函数。
 
此补丁添加了新函数 regexp_count()、regexp_instr()、
regexp_like() 和 regexp_substr()，并扩展了 regexp_replace()
带有一些新的可选参数。所有这些功能都遵循
Oracle 中使用的定义，尽管存在细微差别
由于使用了我们自己的正则表达式引擎，所以在正则表达式语言中——大多数
值得注意的是，默认的换行匹配行为是不同的。
DB2 和其他地方也出现了类似的功能。除了
便于携带，这些功能在某些情况下更容易使用
任务比我们现有的 regexp_match[es] 函数。
 
Gilles Darold，由我大量修改
 
讨论：https ://postgr.es/m/fc160ee0-c843-b024-29bb-97b5da61971f@darold.net
复制

=$ select regexp_count('depesz DEPESZ depesz DepEsz', 'de.e');
 regexp_count 
--------------
            2
(1 row)
 
=$ select regexp_count('depesz DEPESZ depesz DepEsz', 'de.e', 2);
 regexp_count 
--------------
            1
(1 row)
 
=$ select regexp_count('depesz DEPESZ depesz DepEsz', 'de.e', 1, 'i');
 regexp_count 
--------------
            4
(1 row)
复制

=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '..[aeiou]' ) as p from input)
select ri.p, substr( input.i, ri.p) from input, ri;
 p |          substr          
---+--------------------------
 3 | cdefghijklmnopqrstuvwxyz
(1 row)
复制

=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '..[aeiou]', 4 ) as p from input)
select ri.p, substr( input.i, ri.p ) from input, ri;
 p |        substr        
---+----------------------
 7 | ghijklmnopqrstuvwxyz
(1 row)
复制

=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '..[aeiou]', 1, 3 ) as p from input)
select ri.p, substr( input.i, ri.p) from input, ri;
 p  |     substr     
----+----------------
 13 | mnopqrstuvwxyz
(1 row)
复制

=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '..[aeiou]', 1, 3, 1 ) as p from input)
select ri.p, substr( input.i, ri.p) from input, ri;
 p  |   substr    
----+-------------
 16 | pqrstuvwxyz
(1 row)
复制

=$ select regexp_instr('aaa', 'b', 1, 1, 1);
 regexp_instr 
--------------
            0
(1 row)
复制

=$ select regexp_instr('abc', 'c', 1, 1, 1);
 regexp_instr 
--------------
            4
(1 row)
复制

=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '.(.[aeiou])(..)', 1, 1, 0, '') as p from input)
select ri.p, substr( input.i, ri.p) from input, ri;
 p |          substr          
---+--------------------------
 3 | cdefghijklmnopqrstuvwxyz
(1 row)
 
=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '.(.[aeiou])(..)', 1, 1, 0, '', 1) as p from input)
select ri.p, substr( input.i, ri.p) from input, ri;
 p |         substr          
---+-------------------------
 4 | defghijklmnopqrstuvwxyz
(1 row)
 
=$ with input as (select 'abcdefghijklmnopqrstuvwxyz'::text as i),
ri as (select regexp_instr( i, '.(.[aeiou])(..)', 1, 1, 0, '', 2) as p from input)
select ri.p, substr( input.i, ri.p) from input, ri;
 p |        substr         
---+-----------------------
 6 | fghijklmnopqrstuvwxyz
(1 row)
复制

select regexp_instr( 'abcdefghijklmnopqrstuvwxyz', '.(.[aeiou])(..)', subexpr => 2 )
复制

select regexp_instr( 'abcdefghijklmnopqrstuvwxyz', '.(.[aeiou])(..)', 1, 1, 0, '', 2 )
复制

=$ select regexp_like( 'AAA', 'a+' ), regexp_like( 'AAA', 'a+', 'i');
 regexp_like | regexp_like 
-------------+-------------
 f           | t
(1 row)
复制

=$ select n as nth, s as subexpression, regexp_substr( 'abcdefghijklmnopqrstuvwxyz', '.(.[aeiou])(..)', 1, n, '', s )
from generate_series(1,3) n, generate_series(0, 2) s;
 nth | subexpression | regexp_substr 
-----+---------------+---------------
   1 |             0 | cdefg
   1 |             1 | de
   1 |             2 | fg
   2 |             0 | mnopq
   2 |             1 | no
   2 |             2 | pq
   3 |             0 | stuvw
   3 |             1 | tu
   3 |             2 | vw
(9 rows)
复制

=$ select regexp_replace('hubert xxx lubaczewski', '(\S+)', 'depesz', 1, 2);
      regexp_replace       
---------------------------
 hubert depesz lubaczewski
(1 row)
复制