How to capture pattern mtach line and next n line in postgresql

user_15603477 0
Viewed 53
select regexp_replace(
E'In most cases regexp_matches() should be used with the g flag,' ||
E'since if you only want\n the first match, it''s easier and more efficient'||
E'to use regexp_match(). \nHowever, regexp_match() only exists in '||
E'PostgreSQL version 10 and up. When working in older\n versions,' ||
E'a common trick is to place a regexp_matches() call in a sub-select,'||
E'for\n example:', E'(.|\n)*?(PostgreSQL version 10)', E'\n');

Return

                                  regexp_replace                                  
----------------------------------------------------------------------------------
                                                                                 +
  and up. When working in older                                                  +
  versions,a common trick is to place a regexp_matches() call in a sub-select,for+
  example:
(1 row)

But I'm looking for a generic regex pattern that will capture pattern line (whole line) and following n line.(n as a integer, can be 1 or 2 or 3).

I even tried:

select regexp_replace(
E'In most cases regexp_matches() should be used with the g flag,' ||
E'since if you only want\n the first match, it''s easier and more efficient'||
E'to use regexp_match(). \nHowever, regexp_match() only exists in '||
E'PostgreSQL version 10 and up. When working in older\n versions,' ||
E'a common trick is to place a regexp_matches() call in a sub-select,'||
E'for\n example:', E'.*?(PostgreSQL version 10).*?\n', E'\n');

expected result:
Match one line: The whole line contain string "PostgreSQL version 10" will become newline, others remain the same.

Match two line: The whole line contain string "PostgreSQL version 10" will become newline, the following line will also become new line, others remain the same.

postgresql regex
1 Answers

I think this regex should work:

(\s\\n).*(PostgreSQL version 10)(.*?(\\n\s)){T}

where T is the number of line you would like replaced with \n. Demo here.

Explanation:

  • (\s\\n) - matches the string starting with a space, \ and n.

  • .* - match anything between

  • (PostgreSQL version 10) - match the words PostgreSQL version 10

  • (.*?(\\n\s)) - .*? matches any character except line terminator between 0 and unlimited times. \\ matches for \ while n does the same for char n. \s matches whitespace char.

  • {T} - matches the last group i.e. (.*?(\\n\s)) T times.

Edit:

As highlighted by OP, above regex doesn't work in Postgres 14 and 15, updated and tested regex that works in Postgres is: (\s\n).*?(PostgreSQL version 10)(.*?(\n\s)){T}. DB Fiddle Demo

user_19737350
Related