Regular expression - find a regular expression in vim

Question

The original text is simplified as follows (several table creation statements, some tables have the same fields):

CREATE TABLE `test1` (
  `field1` int,
) ENGINE=InnoDB

CREATE TABLE `test2` (
  `field1` int,
) ENGINE=InnoDB

CREATE TABLE `test3` (
  `field2` int,
) ENGINE=InnoDB

CREATE TABLE `test4` (
  `field3` int,
) ENGINE=InnoDB

CREATE TABLE `test5` (
  `field2` int,
) ENGINE=InnoDB

I need to select the table creation statement with the field2 field in the table, that is, select the following text

CREATE TABLE `test3` (
  `field2` int,
) ENGINE=InnoDB

CREATE TABLE `test5` (
  `field2` int,
) ENGINE=InnoDB

I thought of a regular expressionCREATE\_.\{-}F_class_type\_.\{-}ENGINE, but there is obviously a problem with this.

How to add restrictions so that there is only one CREATE in the selected text, so that the selected text is correct. Thanks.

---

I checked again and just used negative look around.

Answer 1

I checked the regular rules again and found that using negative lookaround can solve this problem.
Post the correct regex first: vCREATE(_.(CREATE)@!){-}field2_.{-}ENGINE.*

Explain it, it will also be convenient for you to check later
v: No backslash is required for any metacharactersv：任何元字符都不用加反斜杠
_.：包括换行符的所有字符
(CREATE)@!：顺序否定环视
(_.(CREATE)@!){-}：非贪婪匹配任意字符，并且匹配出的结果中不含有CREATE_.: All characters including line breaks

( CREATE)@!: Sequential negative lookaround

(_.(CREATE)@!){-}: Non-greedy matching of any characters, and the matched result does not contain CREATEstrings

🎜Using negative lookaround can ensure that the matching result will only have one 🎜string, that is, the matching result will not have multiple table-building statements🎜

Answer 2

Another idea: It should also be possible to use 宏

 :vim some.sql
 /field2
 qa{V}:w! >> wanted.sql
 nq
 99@a

• The premise is that there must be no blank lines between the lines of each creation statement, and there must be blank lines at the beginning and end. See :h {
• 99@a 中的99 可以通过 %/field2//n Get

Answer 3

I wrote one casually and passed the test in sublime. For reference:

CREATE TABLE .*s.*`field2`[sS]*?ENGINE=InnoDB